Stored Proceudure: one parameter variable for multiple entries

Question

Post reply

Stored Proceudure: one parameter variable for multiple entries

ichiyo85

SSC Veteran

Points: 286
More actions
March 31, 2011 at 9:19 am

#248063

Hi experts,
I want to make a stored procedure where I have something like:
create proc month (
@month nvarchar (255))
as
select * from table 1
where Period in (@month)
I don't know how many months users want to enter, they could have
Period in ('January'), or
Period in ('January', 'March') or
Period in ('January', 'March', 'July') ... etc
exec proc month 'January, March'
wouldn't return anything. How can I use only one parameter (@month) but have several entries when I execute the stored procedure? Any advice is appreciated. Thank you very much!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lamprey13 SSCertifiable Points: 6190 More actions · Answer 1

Here is a link that talks about amny different ways of dealing with arrays is SQL:

http://www.sommarskog.se/arrays-in-sql-2008.html

My (and I think others) preferred method is to use table-valued parameters.

robinrai3 SSCrazy Points: 2810 More actions · Answer 2

quick and dirty use sp_executesql

assuming months param @monthlist is entered with comma delimiter i.e 'Jan, Feb, Dec' or 'Jan'

somrting like this....

declare @sqlstring varchar(255)

SET @sqlstring = 'SELECT * FROM blahblah WHERE Month in (''' + @monthlist + ''')'

execute sp_executesql @sqlstring

remember to double up single quotes in the string..to get a quote after '(' and before ')'

also there is no validation on the string entered that it follows the comma delimiter format otherwise we are stuffed...that why its dirty

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Oh, be careful, now. That looks like a prime candidate for SQL Injection. A better way would be to split the incoming parameter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 4

How about table parameter? If you can fill the months in a table and pass it as a table parameter, you can give a WHERE condition IN (SELECT month from @TableVar).

_____________________________________________
One ounce of practice is more important than tonnes of dreams