Using the IN operator with INT Fields and a 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 operator with INT Fields and a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-26 : 11:26:57

Jeff writes "Okay, I think this one's pretty tough. I'm a pretty good query writer and this one's had me stumped for a long time...

On our site we allow the users to select products from a list of checkboxes. Those ProductIds are then passed to a stored procedure which returns products corresponding to the checked product ids. When I pass in the product ids and use them in the query, it returns no records. When I use the product ids and create a string which I then EXECUTE, I get results.

If you execute the following queries you will see the problem.

Thanks for your help.
--Jeff

p.s. Windows 2000 Server, SQL Server 2000


USE Northwind
GO

/* A straight query works */
SELECT ProductName 
FROM Products 
WHERE ProductId IN (1,2,3)

GO

/* or */

SELECT ProductName 
FROM Products 
WHERE CAST(ProductId As VarChar(5)) IN ('1','2','3')

GO

/* Creating a string and then using EXEC to execute the string works */
CREATE PROC GetProductsString
	(@ProductIds varchar(100))
AS
	DECLARE @s VarChar(500)
	SET @s = 'SELECT ProductName 
		FROM Products 
		WHERE CAST(ProductId As VarChar(5)) IN (' + @ProductIds + ')'
	EXEC (@s)
GO

EXEC GetProductsString '1,2,3'

GO

/* But using the product ids in a "raw" query doesn't work */
CREATE PROC GetProducts
	(@ProductIds varchar(100))
AS
	-- this didn't work either
	--SET @ProductIds = '''' + REPLACE(@ProductIds, ',', ''',''') + ''''
	SELECT ProductName 
	FROM Products 
	WHERE CAST(ProductId As VarChar(5)) IN (@ProductIds)
GO

EXEC GetProducts '1,2,3'

GO

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-26 : 11:34:12

Take a look at the articles written on CSV. I usually find that the best way to handle this is to parse your CSV string into a temp table and then in your query, join to the temp table.

Jay White
{0}

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-26 : 12:05:35

 
CREATE PROC GetProducts
	(@ProductIds varchar(100))
AS

	SELECT ProductName 
	FROM Products 
	WHERE ProductId IN 
        (select value from dbo.fn_Split(@ProductIds,','))
GO

fn_Split is found at :
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp[/url]

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources