Stored Procedures and String Parameters

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

Transact-SQL (2000)

Stored Procedures and String Parameters

Author

Topic

kykiske25
Starting Member

1 Post

Posted - 2004-06-11 : 10:07:59

Hi, I'm a newbie Transact SQL coder and I'm having a problem with the following code:

CREATE PROCEDURE GetRulePriceFactor
@IdList char(50)

AS

SELECT *
FROM Rules
WHERE Id IN (@IdList)
AND Priority = (SELECT MIN(Priority) FROM Rules WHERE Id IN (@IdList))
GO

The parameter I'm passing as IdList looks something like this:
'888001','808080','880090'
This resides all in one string. What i want the code to do is basically search through the database for ids that match the ones in the string i'm passing

My problem is I'm not getting anything from this code. No errors, no data, just nothing.

However if I change the string i'm passing to contain
'8880001' (or any string containing just one id)
it'll return the correct result.

Any help would be greatly appreciated.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-11 : 10:17:43

You'll have to turn the whole thing into dynamic SQL to do this..

DECLARE @cmd varchar(1000)

SELECT @cmd='SELECT * FROM Rules WHERE ID IN ('+@IDList+
') AND Priority=(SELECT MIN(Priority) FROM Rules WHERE Id IN ('+@IDList+'))'

EXEC (@cmd)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources