Click here to Skip to main content
15,903,854 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
I have a sp where i need to pass a parameter which is using IN Clause

below query is working fine

SQL
select name from tbl where Id='001' and locationId IN (1,2,3))


but this query is giving error

SQL
Declare @var NVARCHAR(100) = '1,2,3';
Declare @whereClause NVARCHAR(100);
SET @whereClause = 'where Id='001' and locationId IN ('+@Var+')';
Select name from tbl + @whereClause


This is error i am getting
Conversion failed when converting the nvarchar value IN (1,2,3) ' to data type int.

please help me out that where i made mistake


advance thanks for help

What I have tried:

the code which i tried to

SQL
Declare @var NVARCHAR(100) = '1,2,3';
Select name from tbl where Id='001' and locationId IN (@var)
Posted
Updated 28-Apr-23 6:56am
v3

If you are going to try it that way you need to use dynamic SQL.

Have a look at the solutions and links posted on this CP post ... How to pass string array in SQL parameter to IN clause in SQL[^] for a selection of solutions

[EDIT] You can't mix and match between sql and dynamic sql in the way you have tried.
Change what you have to
Declare @var NVARCHAR(100) = '1,2,3';
Declare @whereClause NVARCHAR(100);
SET @whereClause = 'where Id=''001'' and locationId IN (' + @Var + ')';

Declare @sql nvarchar(max) = 'Select name from tbl '+ @whereClause

print @sql -- Note this prints "Select name from tbl where Id='001' and locationId IN (1,2,3)"

EXEC sp_executesql @sql
There are better examples in the link I gave above
 
Share this answer
 
v2
Comments
Ravi Sargam 1-Nov-18 9:54am    
yes with dynamic sql i getting error if i execute normal query i can get exact results.
CHill60 1-Nov-18 10:04am    
You did not post any dynamic sql
Ravi Sargam 1-Nov-18 9:54am    
the solution which given it doesn't work for me
The easy way to do this would be to use an SQL SPLIT function in your statement. This function was added with SQL Server 2016, previous versions would require a User Defined Function to have been installed. If this is the case there are plenty available on sites such as Sql Server Central
Good Read: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[^]

This statement is using the 2016+ built in function:
SQL
DECLARE @ID NVARCHAR(3) = '001'
DECLARE @LocationIDs NVARCHAR(100) = '1,2,3'

SELECT Name
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT(@LocationIDs, ','))


The second option is a little more complicated, requiring SQL User Defined Types as well as different syntax within the ADO call. The advantage is you can pass in more complex types such as DataTables, DataReaders, or iEnumerables

SQL Portion
SQL
CREATE TYPE dbo.UserTableType_INTs AS TABLE( IntValue INT NOT NULL)
GO
CREATE PROCEDURE DBO.usp_MyProcedure (
  @ID          NVARCHAR(100),
  @LocationIDs dbo.UserTableType_INTs READONLY
) AS
BEGIN
  SELECT Name
  FROM   Table
  WHERE  Id = @ID
  AND    LocationID IN @LocationIDs
END
GO


And calling it via ADO in C#
C#
// Sample datatable, I generally don't do this and my syntax may be off
DataTable LocationIDs = new DataTable();
LocationIDs.Clear();
LocationIDs.Columns.Add("IntValue");
LocationIDs.Rows.Add(1);
LocationIDs.Rows.Add(2);
LocationIDs.Rows.Add(3);

SqlCommand cmd = new SqlCommand(sqlInsert, connection);
cmd.CommandType = CommandType.StoredProcedure;

// standard input parameter shortcut
cmd.Parameters.AddWithValue("@ID", "001"); 

// different parameter addition when using an SQL User Type
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@LocationIDs", LocationIDs); 
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.UserTableType_INTs";
 
Share this answer
 
v2
Comments
CHill60 1-Nov-18 12:36pm    
Are you sure about that first example (STRING_SPLIT)? Apart from the double-quote/single-quote issue shouldn't it be
SELECT Name 
FROM   Table
WHERE  Id = @ID
AND    LocationID IN (SELECT value FROM STRING_SPLIT (@LocationIDs, ','))
MadMyche 1-Nov-18 12:51pm    
Yes, could have sworn I had that in there; thank you, answer updated
CHill60 1-Nov-18 15:00pm    
:thumb up:
You canot use @var in IN statement unless its dynamic SQL.

Declare @var NVARCHAR(100) = '1,2,3';
declare @sql varchar(300) = 'SELECT * FROM TBL WHERE ID IN (' + @var + ');';
exec(@sql)


Orelse you can use split function of string to split @var and use directly.
 
Share this answer
 
Comments
Ravi Sargam 2-Nov-18 3:53am    
thank u for u r answer, i just made a stupid mistake, i had forgot about use exec(@sql)
CHill60 2-Nov-18 7:20am    
I posted this 17 hours before this member posted their solution after you commented that my original solution didn't work!
Ravi Sargam 2-Nov-18 8:38am    
both are same @CHill60 , u r answer alos accepted thank u for answer..... :)
This is awesome answer.

SELECT value FROM STRING_SPLIT(@LocationIDs, ',')

I have been searching a lot and this was the best solution I got. Thanks a lot
 
Share this answer
 
Comments
CHill60 2-May-23 5:44am    
Please don't post comments as solutions. You can use the "Have a Question or Comment?" link next to a solution to comment on it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900