Variable use in a IN function

Question

I am wondering if anyone can help me with this query.

declare @empno varchar(11)

select @empno = '''128''' + ',' + '''180'''

--print @empno (It prints out '128','180')

SELECT Received.[Training Course], Received.[Training Provider], Received.[Date of Training],

Received.[Accreditation Received], Received.[Training Completed], Received.Comments,

Received.ExpiryDate,Received.Year, Received.Month, viewEmployee.State,

viewEmployee.Employee, Received.EmpNo, viewEmployee.EmpNo AS Expr1

FROM Received INNER JOIN

viewEmployee ON Received.EmpNo = viewEmployee.EmpNo

WHERE (Received.EmpNo IN (@empno))

If I type '128','180' in the place of the variable it works. But will return nothing if I use the variable.

 

David

 

Answer 1

You have two ways to let the code work:

1.
Create a table variable, insert the empno list into that table and then use it in the where clause:

declare @tmp table(empno int)
insert into @tmp (empno) select empno from....
select ....
where (received.empno in (select empno from @tmp))


2.
Create a dynamic sql and then execute it:
declare @empno varchar(11)
select @empno = ...
declare @dynsql nvarchar(1000)
set @dynsql = 'select .... where (received.empno in (' + @empno + ')'
exec(@dynsql)

Answer 2

David,

Values cannot be passed into an "IN" clause dynamically as you have done, although the syntax may turn out to be perfectly right. An alternative to this could be creating a udf, which splits the comma separated value into a table, and rephrasing your where clause as "Received.EmpNo in (Select Col from udf_SplitComma(@Empno))".

Let me know if you need the code for the udf.

Another crude alternative would be converting the whole query into a dynamic sql, but that would be ugly as the very purpose of creating a Stored proc would be killed.. and it would also give rise to problems of sql-injection.

Answer 3

This will work for, what you want.

Try this

--Start Code

declare @empno varchar(11)

declare @sqlvarchar(11)

select @empno = '''128''' + ',' + '''180'''

print @empno

set @sql = ' SELECT Received.[Training Course], Received.[Training Provider], Received.[Date of Training],

Received.[Accreditation Received], Received.[Training Completed], Received.Comments,

Received.ExpiryDate,Received.Year, Received.Month, viewEmployee.State,

viewEmployee.Employee, Received.EmpNo, viewEmployee.EmpNo AS Expr1

FROM Received INNER JOIN

viewEmployee ON Received.EmpNo = viewEmployee.EmpNo

WHERE (Received.EmpNo IN (' + @empno + '))'

PRINT @sql

EXEC(@sql)

--End Code

 

Gurpreet S. Gill

 

Answer 4

Thanks for all your help guys, based on the info you all provided I was able to get it working.

 

Sorry I took so long responding as time got away from me.

 

David.

Answer 5

Please don't use dynamic SQL for this simple problem. Use of dynamic SQL has security implications, performance issues depending on your case and manageability issues. Write a TSQL table-valued function that can split a string into list of values and use it in the WHERE clause like:

 

where exists(

 select * from split_str (@empnos) as s

 where s.value = Received.EmpNo

)

 

The TSQL TVF solution is not only safer to use but it also makes the code easier to read & manage. See the link below for various solutions for splitting strings:

 

http://www.sommarskog.se/arrays-in-sql.html