Passing multiple values ​​for a single SQL parameter

Question

I have a CheckBoxList where users can select multiple items from a list. Then I need to pass these values ​​to my stored procedure so that they can be used in the WHERE clause, for example:

WHERE ID IN (1,2,3)

I tried to do this to its nvarchar parameter and I passed the string 1,2,3using:

WHERE ID IN (@IDs)

But this led to the following error:

Conversion failed when converting the nvarchar value '1,2,3' to data type int

Any help would be greatly appreciated!

Answer 1

There are several ways to do this. You can pass this parameter as an XML block, like this example:

CREATE PROCEDURE [dbo].[uspGetCustomersXML]
    @CustomerIDs XML
AS
BEGIN
SELECT c.ID, c.Name
FROM [dbo].[Customer] c
JOIN @CustomerIDs.nodes('IDList/ID') AS x(Item) ON c.ID = Item.value('.', 'int' )
END
GO

--Example Use:
EXECUTE [dbo].[uspGetCustomersXML] '<IDList><ID>1</ID><ID>10</ID><ID>100</ID></IDList>'

CSV split, ( , ).

CREATE PROCEDURE [dbo].[uspGetCustomersCSV]
    @CustomerIDs VARCHAR(8000)
AS
BEGIN
SELECT c.Id, c.Name
FROM [dbo].[Customer] c
JOIN dbo.fnSplit(@CustomerIDs, ',') t ON c.Id = t.item
END
GO

--Example Use:
EXECUTE [dbo].[uspGetCustomersCSV] '1,10,100'

SQL 2008 , , TABLE . blogged , .

Answer 2

alter procedure c2
(@i varchar(5))
as
begin
    declare @sq nvarchar(4000)
    set @sq= 'select * from test where id in (<has_i>) '
    SET @sq= REPLACE(@sq, '<has_i>', @i)
    EXECUTE sp_executesql  @sq
end

exec c2 '1,3'

Answer 3

. , , .

If you have a problem converting it to a stored procedure call, just let me know.