-1

I have to pass a list of integers as String from front end to a SQL Server database, e.g. : (2,3,4,5)

ALTER PROCEDURE GetMultipleCustomerByID
    @Id nvarchar(50)
AS
BEGIN
    SELECT
        Firstname, Lastname 
    FROM
        Customer 
    WHERE
        CustomerID IN (@Id)
END

Exec GetMultipleCustomerByID "2,3,4,5"

Above represented values (2,3,4,5) are ID values (primary key) of the table.

From these id, I have to retrieve the rows from the table.

The string "2,3,4,5" may be dynamic. May include more than or less than 4 numbers.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andy
  • 35
  • 8
  • it's a string, so you'd need string operations to slice things apart on the `,`, which means you need a loop to do this iteratively. That or you use dynamic sql, and build a new query statement, interpolating your string into the new query. e.g `@sql = 'SELECT ... in (' + @id + ')'; exec @sql;`-type stuff. – Marc B Sep 13 '16 at 14:10
  • i dont know how to iterate, Can you please give the Solution .. – Andy Sep 13 '16 at 14:12
  • he already give you the solution, use a `dynamic sql` – Juan Carlos Oropeza Sep 13 '16 at 14:12

1 Answers1

1

There are multiple ways to solve this problem, but one simple way is dynamic SQL:

ALTER PROCEDURE GetMultipleCustomerByID (
    @Ids nvarchar(50)
) as
BEGIN
    declare @sql nvarchar(max);

    set @sql = 'select Firstname, Lastname from Customer where CustomerID in (@Ids)';
    set @sql = replace(@sql, '@Ids', @Ids);

    exec sp_executesql @sql;
END;

Exec GetMultipleCustomerByID "2,3,4,5";
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786