Need help with sp_executesql and the Keyword IN ()

[MonsterBuck]

I can use the Execute command and the keyword IN() and it works, but when I try and covert the code to sp_executesql I get an error message

Below is just some sample code to set up a test case senerio.

Does anybody know how to use sp_executesql and the keyword IN()? Is this possible?
--******************************************************
create table deleteme (i int)

insert into deleteme
values (1)
insert into deleteme
values (2)
insert into deleteme
values (3)

GO
--this plain execute works
declare @list varchar(50)
set @list = '1,2,3,4'
exec ('select * from deleteme where i in ('+@list+')')


--this works fine with @list only has one value
set @list = '1'
execute sp_executesql
N'select * from deleteme where i in (@listvar)',
N'@listvar varchar(50)',
@listvar = @list

--this doesn't work
--I get the following error message
--Server: Msg 245, Level 16, State 1, Line 1
--Syntax error converting the varchar value '1,2,3,4' to a column of data type int.

set @list = '1,2,3,4'
execute sp_executesql
N'select * from deleteme where i in (@listvar)',
N'@listvar varchar(50)',
@listvar = @list
GO

drop table deleteme

 

[swampBoogie]

http://www.tek-tips.com/faqs.cfm?spid=183&sfid=3979

 

[MonsterBuck]

So are you saying that there is no way you can use sp_executesql and in the where clause use the keyword IN ()?

I have a solution that is working. I just like the idea of using sp_executesql and was hoping there was a way to use it.

 

[JamesLean]

To do this you need to build the SQL string first before passing it to sp_executesql:

set @list = '1,2,3,4'

set @sql = N'select * from deleteme where i in (' + @listvar + ')'

execute sp_executesql @sql

--James

 

[MonsterBuck]

Is there any advantage of that code verses this code?
set @list = '1,2,3,4'
exec ('select * from deleteme where i in ('+@list+')')

 

[JamesLean]

Not that I am aware of. I'm sure if there are any advantages of one method over the other then they would be very minimal.

--James

 

[MonsterBuck]

That makes sense to me.

Thanks.

 

[DiegoErnesto]

Hi;

when you use sp_executesql with parameters is not equal to concatenate string. If you try the statements below verify this. The advantage is that sp_executesql more efficient because it generates execution plans that are more likely to be reused by SQL Server.


declare @list varchar(50)
declare @list2 varchar(50)
set @list = '1'
set @list2 = '2'
execute sp_executesql
N'select * from deleteme where i in (@listvar, @listvar2)',
N'@listvar varchar(50), @listvar2 varchar(50)',
@listvar = @list, @listvar2 = @list2


Good luck...

 

[MonsterBuck]

The problem is that I don't know how many values are in the comma delimited variable.
That is why I was hoping that there was some way to use the IN keyword and sp_execute without having to break up the comma delimited variable. I like the efficiency of sp_executesql.

 

[JamesLean]

Have a look at this FAQ to give you some options:

Passing a list of values to a Stored Procedure
faq183-3979

--James