String from parameter into IN list

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)

String from parameter into IN list

Author

Topic

jzurbo77
Starting Member

21 Posts

Posted - 2008-06-18 : 13:00:45

I hope that is a very simple question...

I have a very simple query which works just fine:


SELECT	top 3  Result
FROM	gbdb.dbo.Tests
WHERE Result IS NOT NULL 
	AND Var_Id in (392,514)

I need to be able to get the list of Var_Id from a table as a string and use it in this quesry as a parameter:

Query 1:

Select varlist from vartable where varid = 5

It returns, say, the string '392,514'

I want to do following:


Alter Procedure spGetResults
@txtParm_Vars varchar(20)
AS
Begin
Set Nocount ON;
SELECT	top 3  Result
FROM	gbdb.dbo.Tests
WHERE Result IS NOT NULL 
	AND Var_Id in (@txtParm_Vars)
END

This one does not work - it complains that
Syntax error converting the varchar value '392,514' to a column of data type int.

Yes, Var_Id is defined as Int in table Tests, but why it is OK hard code '392, 514' as the IN list and it is not OK to supply same string as a value of a parameter? What would be a solution here?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-18 : 13:08:04

[code]Alter Procedure spGetResults
@txtParm_Vars varchar(20)
AS
Begin
Set Nocount ON;
SELECT top 3 Result
FROM gbdb.dbo.Tests
WHERE Result IS NOT NULL
AND ',' + @txtParm_Vars + ',' LIKE '%,'+ CAST(Var_Id AS varchar(10)) + ',%'
END[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-21 : 21:30:59

Many other methods
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources