Passing parameter to IN SQL statement

Question

I collected some values to be looked up from a DB column inside a string variable and was trying to pass this as a parameter in the SQL StoredProcedure.

ALTER PROCEDURE [dbo].[InkDB]
(
@ser nvarchar(255),
@svt nvarchar(255)
)
AS
SELECT DISTINCT Details from tbData WHERE (Name IN @svt AND Address=@ser)

This gives me a syntax error near @svt message while trying to run the query.

From my webpage, the parameter has value something like ('PersonA', 'Person B', 'Person C') that is being passed. How do I use the IN statement in this case?

Answer 1

I would do it with XML. Could not find this solution in the duplicate question so I add it here.

Your SP could look like this:

alter procedure InkDB
  @ser nvarchar(255),
  @svt xml
as

declare @T table
(
  Name nvarchar(50)
)

insert into @T
select T.N.value('.', 'nvarchar(50)')
from @svt.nodes('/N') as T(N)

select distinct Details
from tbData 
where Name in (select Name from @T) and
      Address=@ser

And you would call it like this.

exec InkDB '', '<N>PersonA</N><N>PersonB</N>'

Answer 2

Dynamic Query

Alter procedure test
(
    @ser nvarchar(255),
    @svt nvarchar(255)
)
AS
BEGIN
   declare @sql nvarchar(Max)

   Set @sql='SELECT DISTINCT semester_code from mst_paper WHERE course_code IN ('+@svt+') AND branch_code='+@ser+''

   exec sp_executesql @sql
END

Answer 3

Its a common mistake - you are passing a single value (expression) of type string to IN operator but IN expects a comma delimited list of values (expressions) and not a single string variable.

What you need to do here is to have a function that would split the given parameter into a multiple values based on given delimiter and then use that list with IN keyword. For example,

SELECT DISTINCT Details from tbData WHERE Name IN (SELECT Val FROM dbo.efn_Split(@svt, ',')) AND Address=@ser

where efn_Split is a table value function that will split comma-separated values into a table. See these various SO questions for implementation of such function:
Split function equivalent in T-SQL?
How to split string using delimiter char using T-SQL?

Yet another alternative is to construct the SQL statement and execute with sp_executesql.

Answer 4

IN needs to be as follows:

... IN (@param1, @param2, ...)

So, you should do:

SELECT DISTINCT Details from tbData WHERE Name IN (@svt) AND Address=@ser

Update:

The alter procedure statement you provided in your question is syntactically incorrect. My answer provides the correct syntax for writing the statement and it compiles.

Reading your question over again, I see you have in fact have two issues. The first was a syntax error and the second passing in a comma delimited list in a single parameter.

The answer is you simply cannot provided a comma delimited list of values at runtime into a single string type parameter that is used in the IN (...) clause. Now, on this second point, I would argue that this is not a good design/programming approach to the problem, but it can be done using dynamic SQL or parsing out each value from the string parameter, store them into a temp table then revise your query to join to that, or use a (or use a table valued function and store the parsed items there, where it can be queried from.

Below is the corrected syntax for your code, but it would not solve the second aspect of passing in a string containing a comma delimited list of values. That could be solved as I described above.

For the syntax error, first, you can create a dummy table to test your code. Note, a typical database table should have a primary key. This is strictly a dummy table to test the statement:

CREATE TABLE TbData( Name nvarchar(255), Details nvarchar(255), Address nvarchar(255) );

Then, you can create the initial stored procedure:

CREATE PROCEDURE Test ( @ser nvarchar(255), @svt nvarchar(255) ) AS BEGIN SELECT DISTINCT Details FROM tbData WHERE Name IN (@ser) AND Address = @svt END

And finally, execute the alter stored procedure statement you had asked about:

ALTER PROCEDURE Test ( @ser nvarchar(255), @svt nvarchar(255) ) AS BEGIN SELECT DISTINCT Details FROM tbData WHERE Name IN (@ser) AND Address = @svt END