Can I pass a list as a parameter to a stored procedure? 1

[j9]

I would like to pass a list of values for use in a
'WHERE IN (x,y,z)' clause w/in my stored procedure.

Here's the concept I'm looking for, but I have no idea if it can be done or how to do it:

CREATE PROCEDURE spMySP @myList varchar(50) AS
SELECT *
FROM myTable
WHERE myColumn IN (@myList)

-------
EXEC spMySP @myList='"EQF","EQM","EQS"'

Thanks for any help!

 

[JohnDTampaBay]

Check out thread183-205853 --Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[tlbroadbent]

See faq183-3132, thread183-447149, and thread183-450813. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[j9]

Thanks, I tried to use 'EXEC' before I posted but I guess I just don't understand its scope well enough. I still cannot figure out where to set the @sql variable in my sp and where to code 'EXEC @sql'. My sp is below and I'm sure it looks sloppy to the pro's out there, but I'm pretty much a novice...

CREATE PROCEDURE sr_Wards @gradYr varchar(4),@rotList varchar(50),@wkID INT AS
SET NOCOUNT ON

/* This cursor will be for students who are eligible for the given ward */
DECLARE cursRandStudents CURSOR FOR
SELECT DISTINCT s.int_ref_id,s.namelast,s.namefirst,numDuty=0,numBackup=0
FROM ((spSchedule sch JOIN rotStudent s ON sch.int_ref_id=s.int_ref_id)
JOIN spClassRotation cr ON cr.classRotID=sch.classRotID)
JOIN spRotation r ON r.RotationID=cr.RotID
WHERE s.classOf=@gradyr AND
Abbreviation IN (@rotList)

/* holding variables*/
DECLARE @int_ref_id varchar(11),
@namelast varchar(50),
@namefirst varchar(50),
@numDuty INT,
@numBackup INT

/*temp table for holding random list of students who are eligible for the given ward.*/
CREATE TABLE ##tmpRandStudent (
random numeric(18,18),
int_ref_id varchar(11),
namelast varchar(50),
namefirst varchar(50),
numDuty INT,
numBackup INT
)

OPEN cursRandStudents
FETCH NEXT FROM cursRandStudents INTO @int_ref_id,@namelast,@namefirst,@numDuty,@numBackup
WHILE (@@fetch_status <> -1) BEGIN
INSERT INTO ##tmpRandStudent (random, int_ref_id,namelast,namefirst,numDuty,numBackup) values (rand(), @int_ref_id,@namelast,@namefirst,@numDuty,@numBackup)
FETCH NEXT FROM cursRandStudents INTO @int_ref_id,@namelast,@namefirst,@numDuty,@numBackup
END

SELECT *
FROM ##tmpRandStudent
ORDER BY random


DROP TABLE ##tmpRandStudent
CLOSE cursRandStudents
DEALLOCATE cursRandStudents
GO

 

[j9]

Nevermind--I finally figured it out. Thanks!

 

[sunila7]

Hi,

I think u need to open cursor here for wht u r doing....
Try this SP instead....

CREATE PROCEDURE sr_Wards @gradYr varchar(4),@rotList varchar(50),@wkID INT AS
SET NOCOUNT ON
Declare @SQL varchar(1500)

SET @SQL = 'INSERT INTO ##tmpRandStudent
SELECT DISTINCT s.int_ref_id,s.namelast,s.namefirst,numDuty=0,numBackup=0
FROM ((spSchedule sch JOIN rotStudent s ON sch.int_ref_id=s.int_ref_id)
JOIN spClassRotation cr ON cr.classRotID=sch.classRotID)
JOIN spRotation r ON r.RotationID=cr.RotID
WHERE s.classOf=@gradyr AND
Abbreviation IN (' + @rotList + ')'

exec (@SQL)


Sunil

 

[j9]

Are you saying that I shouldn't use cursor? I need to use it to randomize my students.

 

[sunila7]

Hi,

oops,I didnt see the rand function there... I tried to get an alternate way of doing it, but was not really convinced that it will work in all cases..

Sunil