IN in stored procedure..?

[Kujen]

Hi all,

I have a query that works fine with:

select * from Table1
where Field1 in ('47740','47993')

But if I replace '47740','47993' with a parameter in a stored procedure:

select * from Table1
where Field1 in (@Parameter1)

... I get "too many arguments specified."

How can I tell the stored procedure that it's only one argument? Is there a workaround?

Kent J.

 

[bborissov]

You can't use the list directly.
You have two choices:
1. Use so called Dynamic SQL:

DECLARE @sql varchar(8000)
SET @sql = 'select * from Table1
                  where Field1 in ('+@Parameter1+')'
EXEC(@sql)

But this is the performance killer and also you are opened for SQL Injection attacks.

2. You could use dbo.split() function described in that forum and then use the returned table in INNER JOIN.

SELECT Table1.*
FROM Table1
INNER JOIN dbo.Split(@Parameter1) as Splt
ON Table1.Field1 = Splt.NameOfThe field

Just search for dbo.Split() here to get code for the function.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.