T-SQL stored procedure that accepts multiple Id values

Question

Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure    For instance  I want departments 1  2  5  7  20 returned by my stored procedure  In the past  I have passed in a comma delimited list of ids  like the below code  but feel really dirty doing it    SQL Server 2005 is my only applicable limitation I think   create procedure getDepartments    DepartmentIds varchar max  as   declare  Sql varchar max         select  Sql    select  Name  from Department where DepartmentId in       DepartmentIds         exec  Sql

User · Answer

Yeah  your current solution is prone to SQL injection attacks   The best solution that I ve found is to use a function that splits text into words  there are a few posted here  or you can use this one from my blog  and then join that to your table  Something like   SELECT d  Name  FROM Department d     JOIN dbo SplitWords  DepartmentIds  w ON w Value   d DepartmentId

User · Answer

Try This One    list of params varchar 20  -- value 1  2  5  7  20   SELECT d  Name  FROM Department d where  list of params like       CONVERT VARCHAR 10  d Id           very simple

User · Answer

You could use XML    E g    declare  xmlstring as  varchar 100   set  xmlstring     lt args gt  lt arg value  42    gt  lt arg2 gt -1 lt  arg2 gt  lt  args gt     declare  docid int   exec sp xml preparedocument  docid output   xmlstring  select   id  parentid nodetype localname  text  from    openxml  docid    args   1     The command sp xml preparedocument is built in    This would produce the output    id  parentid    nodetype    localname   text 0   NULL        1           args        NULL 2   0           1           arg         NULL 3   2           2           value       NULL 5   3           3            text       42 4   0           1           arg2        NULL 6   4           3            text       -1   which has all  more   of what you you need

User · Answer

Erland Sommarskog has maintained the authoritative answer to this question for the last 16 years  Arrays and Lists in SQL Server   There are at least a dozen ways to pass an array or list to a query  each has their own unique pros and cons     Table-Valued Parameters  SQL Server 2008 and higher only  and probably the closest to a universal  best  approach  The Iterative Method  Pass a delimited string and loop through it    Using the CLR  SQL Server 2005 and higher from  NET languages only   XML  Very good for inserting many rows  may be overkill for SELECTs  Table of Numbers  Higher performance complexity than simple iterative method  Fixed-length Elements  Fixed length improves speed over the delimited string Function of Numbers  Variations of Table of Numbers and fixed-length where the number are generated in a function rather than taken from a table  Recursive Common Table Expression  CTE   SQL Server 2005 and higher  still not too complex and higher performance than iterative method  Dynamic SQL  Can be slow and has security implications   Passing the List as Many Parameters  Tedious and error prone  but simple    Really Slow Methods  Methods that uses charindex  patindex or LIKE    I really can t recommend enough to read the article to learn about the tradeoffs among all these options

User · Answer

A superfast XML Method  if you want to use a stored procedure and pass the comma separated list of Department IDs     Declare  XMLList xml SET  XMLList cast   lt i gt   replace  DepartmentIDs       lt  i gt  lt i gt      lt  i gt   as xml  SELECT x i value      varchar 5    from  XMLList nodes  i   x i     All credit goes to Guru Brad Schulz s Blog

User · Answer

One method you might want to consider if you re going to be working with the values a lot is to write them to a temporary table first   Then you just join on it like normal   This way  you re only parsing once   It s easiest to use one of the  Split  UDFs  but so many people have posted examples of those  I figured I d go a different route     This example will create a temporary table for you to join on   tmpDept  and fill it with the department id s that you passed in   I m assuming you re separating them with commas  but you can -- of course -- change it to whatever you want   IF OBJECT ID  tempdb   tmpDept    U   IS NOT NULL BEGIN     DROP TABLE  tmpDept END  SET  DepartmentIDs REPLACE  DepartmentIDs          CREATE TABLE  tmpDept  DeptID INT  DECLARE  DeptID INT IF IsNumeric  DepartmentIDs  1 BEGIN     SET  DeptID  DepartmentIDs     INSERT INTO  tmpDept  DeptID  SELECT  DeptID END ELSE BEGIN         WHILE CHARINDEX      DepartmentIDs  gt 0         BEGIN             SET  DeptID LEFT  DepartmentIDs CHARINDEX      DepartmentIDs -1              SET  DepartmentIDs RIGHT  DepartmentIDs LEN  DepartmentIDs -CHARINDEX      DepartmentIDs               INSERT INTO  tmpDept  DeptID  SELECT  DeptID         END END   This will allow you to pass in one department id  multiple id s with commas in between them  or even multiple id s with commas and spaces between them   So if you did something like   SELECT Dept Name  FROM Departments  JOIN  tmpDept ON Departments DepartmentID  tmpDept DeptID ORDER BY Dept Name   You would see the names of all of the department IDs that you passed in     Again  this can be simplified by using a function to populate the temporary table    I mainly did it without one just to kill some boredom  -P  -- Kevin Fairchild

[sql-server] T-SQL stored procedure that accepts multiple Id values

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures