SP - Passing list to parameter

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 2005 Forums

Transact-SQL (2005)

SP - Passing list to parameter

Author

Topic

karthik
Starting Member

8 Posts

Posted - 2007-12-20 : 01:45:18

Probelm:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind.Products
WHERE ProductID IN (@ids)
But when they test this:

EXEC get_product_names '9, 12, 27, 37'

error message:
Server: Msg 245, Level 16, State 1, Procedure get_product_names,Line 2
Syntax error converting the varchar value '9, 12, 27, 37' to a column
of data type int.

How to overcome this problem ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-20 : 01:48:53

Make use of a csv splitting function - see this link as an example http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94518

Duane.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 02:05:42

You have to get individual IDs as rows of a table and then INNER JOIN onto this table to filter on passed IDs

write a UDF like this


CREATE FUNCTION SplitIDs
@IDs varchar(8000)
RETURNS @Results TABLE
(ID int)
AS
 DECLARE @N int,@Pos int

WHILE @IDs IS NOT NULL
BEGIN
 SET @Pos=CHARINDEX(',',@IDs)
 CASE
 WHEN @Pos >0 THEN
 SET @N=CAST(LEFT (@IDs,@Pos-1) AS Int)
 ELSE @N=CAST(@IDs AS Int)
 END
 INSERT INTO @Results VALUES (@N)
 SET @IDs= CASE WHEN @Pos >0 THEN RIGHT(@IDs,@Pos+1) ELSE NULL END
END
GO

Then use like this

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind.Products p
INNER JOIN SpiltIDs(@ids) t
ON t.ID = p.ProductID

matty
Posting Yak Master

161 Posts

Posted - 2007-12-20 : 02:09:54

you may use dynamic sql..
CREATE PROCEDURE get_product_names @ids varchar(50) AS
declare @sql nvarchar(500)

set @sql = 'SELECT ProductID, ProductName
FROM Northwind.Products
WHERE ProductID IN (' + @ids + ')'
EXEC Sp_ExecuteSQL @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources