Problem using the IN in Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem using the IN in Procedure

Author

Topic

PS
Starting Member

9 Posts

Posted - 2005-09-01 : 02:32:08

I am getting the error:

Server: Msg 245, Level 16, State 1, Procedure ps_find, Line 7
Syntax error converting the varchar value '(1,2,3,4,5,6,9,10,11,12,13,14,17,33,101,102,103,201,202,203)' to a column of data type int.

when running the following script:

======================================================================================
if exists (Select [name] from sysobjects
where [name] like 'ps_find')
drop procedure dbo.ps_find
go

create procedure dbo.ps_find @Vid as varchar(250)
as
begin
select count(*)
from [Logdata]
where [Mode] = 6 and [ValueID] in (select @Vid)
end
go

declare @Produc as varchar(250)
set @Produc = '(1,2,3,4,5,6,9,10,11,12,13,14,17,33,101,102,103,201,202,203)'
exec dbo.ps_find @Produc
go
==============================================================================

if I try to switch in the line:

where [Mode] = 6 and [ValueID] in (1,2,3,4,5,6,9,10,11,12,13,14,17,33,101,102,103,201,202,203)

in stead of:

where [Mode] = 6 and [ValueID] in (select @Produc)

then it runs perfect.

Need help on sending a text string to a procedure containing the condition for the IN.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-01 : 02:57:18

You need to use Dynamic SQL

Exec('Select columns from yourTable where ValueId in '+@Produc)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-01 : 03:45:47

Or "split" the delimited list and JOIN it to the [Logdata] table

(Using dynamic SQL will require the user to have SELECT permission on the table, rather than just EXECUTE permission on the SProc)

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-04 : 21:56:45

or use patindex or charindex, change your delimiters to square brackets e.g. ([1],[2]...)

and patindex('%[' + valueid + ']%',@vid)>0

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources