How do I pass a list as a parameter in a stored procedure

Question

Looking to pass a list of User IDs to return a list names  I have a plan to handle the outputed names  with a COALESCE something or other  but trying to find the best way to pass in the list of user IDs  The guts of my sproc will look something like this   create procedure  dbo   get user names   user id list  --which would equal a list of incoming ID numbers like  5 44 72 81 126   username varchar  30  output as select last name      first name  from user mstr where user id in  user id list   Passing the values for  user id list is my main concern here

User · Answer

You can try this   create procedure  dbo   get user names       user id list varchar 2000   -- You can use any max length       username varchar  30  output     as     select last name      first name      from user mstr     where user id in  Select ID from dbo SplitString   user id list           And here is the user defined function for SplitString   Create FUNCTION  dbo   SplitString               Input NVARCHAR MAX          Character CHAR 1    RETURNS  Output TABLE         Item NVARCHAR 1000    AS BEGIN       DECLARE  StartIndex INT   EndIndex INT        SET  StartIndex   1       IF SUBSTRING  Input  LEN  Input  - 1  LEN  Input    lt  gt   Character       BEGIN             SET  Input    Input    Character       END        WHILE CHARINDEX  Character   Input   gt  0       BEGIN             SET  EndIndex   CHARINDEX  Character   Input               INSERT INTO  Output Item              SELECT SUBSTRING  Input   StartIndex   EndIndex - 1               SET  Input   SUBSTRING  Input   EndIndex   1  LEN  Input         END        RETURN END

User · Answer

As far as I can tell  there are three main contenders  Table-Valued Parameters  delimited list string  and JSON string   Since 2016  you can use the built-in STRING SPLIT if you want the delimited route  https   docs microsoft com en-us sql t-sql functions string-split-transact-sql  That would probably be the easiest most straightforward simple approach    Also since 2016  JSON can be passed as a nvarchar and used with OPENJSON  https   docs microsoft com en-us sql t-sql functions openjson-transact-sql   That s probably best if you have a more structured data set to pass that may be significantly variable in its schema    TVPs  it seems  used to be the canonical way to pass more structured parameters  and they are still good if you need that structure  explicitness  and basic value type checking  They can be a little more cumbersome on the consumer side  though  If you don t have 2016   this is probably the default best option   I think it s a trade off between any of these concrete considerations as well as your preference for being explicit about the structure of your params  meaning even if you have 2016   you may prefer to explicitly state the type schema of the parameter rather than pass a string and parse it somehow

User · Answer

I solved this problem through the following    In C   I built a String variable       string userId        I put my list s item in this variable  I separated the           for example    in C   userId   5 44 72 81 126      and Send to SQL-Server    SqlParameter param   cmd Parameters AddWithValue   user id list  userId       I Create Separated Function in SQL-server For Convert my Received List  that it s type is NVARCHAR Max   to Table     CREATE FUNCTION dbo SplitInts           List      VARCHAR MAX         Delimiter VARCHAR 255        RETURNS TABLE   AS     RETURN   SELECT Item   CONVERT INT  Item  FROM           SELECT Item   x i value     text    1     varchar max              FROM   SELECT  XML    CONVERT XML    lt i gt               REPLACE  List   Delimiter    lt  i gt  lt i gt        lt  i gt    query                    AS a CROSS APPLY  XML  nodes  i   AS x i    AS y         WHERE Item IS NOT NULL            In the main Store Procedure  using the command below  I use the entry list       SELECT user id   Item FROM dbo SplitInts  user id list

User · Answer

You can use this simple  inline  method to construct a string list type parameter   works in SQL Server 2014    declare  p1 dbo string list type insert into  p1 values N myFirstString   insert into  p1 values N mySecondString     Example use when executing a stored proc   exec MyStoredProc  MyParam  p1

User · Answer

Maybe you could use   select last name      first name  from user mstr where        user id list       like        convert nvarchar  user id

User · Answer

Azure DB  Azure Data WH and from SQL Server 2016  you can use STRING SPLIT to achieve a similar result to what was described by  sparrow   Recycling code from  sparrow  WHERE user id IN  SELECT value FROM STRING SPLIT   user id list         Simple and effective way of accepting a list of values into a Stored Procedure

User · Answer

Check the below code  this work for me  ManifestNoList      VARCHAR MAX    WHERE               ManifestNo IN  SELECT value FROM dbo SplitString  ManifestNoList

User · Answer

The preferred method for passing an array of values to a stored procedure in SQL server is to use table valued parameters   First you define the type like this   CREATE TYPE UserList AS TABLE   UserID INT      Then you use that type in the stored procedure   create procedure  dbo   get user names   user id list UserList READONLY   username varchar  30  output as select last name      first name  from user mstr where user id in  SELECT UserID FROM  user id list    So before you call that stored procedure  you fill a table variable   DECLARE  UL UserList  INSERT  UL VALUES  5   44   72   81   126    And finally call the SP   EXEC dbo get user names  UL   username OUTPUT

[sql] How do I pass a list as a parameter in a stored procedure?

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures