Using the IN() function for a parameter for stored 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

SQL Server 2000 Forums

SQL Server Development (2000)

Using the IN() function for a parameter for stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-28 : 08:15:36

Paul writes "Hi,

Using SQL2000, and VB6. Using Access 2002 as my SQL designer if that matters.

Trying to run a simple Insert statement with a stored procedure, but am getting an error message.

Here is the stored procedure:

INSERT INTO InvoiceLink (InvoiceLink_TransID)
SELECT trans_ID
FROM Trans
WHERE (trans_ID IN (@MyListOfIDS))

Here is the VB6 code:

MyConn.Execute "EXECUTE test2 @MyListOfIDS='10917,29972,29969,10926'"

Here is the resulting error message:
"Syntax error converting the nvarchar value '10917,29972,29969,10926' to a column of data type int."

Any ideas on how get it to recognise it as a normal IN() list?
Any ideas for work arounds?

THANKS!!

Paul"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-28 : 08:19:03

The way i have done this (probably not the best) is to parse the list into its own table variable, then you can use Select ListOfIDs from @MyTableVar for your IN clause.

SamC
White Water Yakist

3467 Posts

Posted - 2005-04-28 : 08:21:05

Unfortunately, SQL doesn't support IN (@Parameter)

but it will support

IN (A, B, C, D,...)

OR

IN (myTable)

Search this forum for solutions. This is a frequent question and it will involve writing an extended stored procedure to convert @Parameter to a rowset.

Sam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-28 : 10:40:24

You could use dynamic SQL:


exec(
'
INSERT INTO InvoiceLink
	(InvoiceLink_TransID)
SELECT
	trans_ID
FROM
	Trans
WHERE
	trans_ID IN ('+@MyListOfIDS'+)
')

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-28 : 10:52:33

see:

http://www.sqlteam.com/item.asp?ItemID=11499

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-28 : 17:19:39

quote:
Originally posted by jsmith8858

see:

http://www.sqlteam.com/item.asp?ItemID=11499

- Jeff

And don't forget to check out the comment section of that article for a set-based solution. The link that Jeff provided uses a UDF with a WHILE loop. Page47 and Joe Celko prove a solution that utilizes a join. I've used this approach several times now. I still don't have any idea of why it works though, it just does. The part in their code that you'd need to copy into yours is:



INNER JOIN
	(
		select nullif(substring(@sep+@csv+@sep,n,charindex(@sep,@sep+@csv+@sep,n)-n),'') as element      
		from dbo.Numbers      
		where 
			n <= datalength(@sep+@csv+@sep) and
			n-datalength(@sep)>0 and
			substring(@sep+@csv+@sep,n-datalength(@sep),datalength(@sep))=@sep and            
			charindex(@sep,@sep+@csv+@sep,n)-n>0
	) csv
ON csv.element = t.YourColumnThatHasTheseValues

Of course it requires a tally table for this solution.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources