using multivalue parameter for stored procedure

Question

using multivalue parameter for stored procedure

Brad Allison

Hall of Fame

Points: 3550
More actions
September 17, 2008 at 5:56 am

#193018

Is there a trick to passing a multivalued parameter to a stored procedure? It has been a while since I have had to do this and I cannot remember. Is this something that I need to do in reporting services passing the string, or is it configured in the T-SQL?
Thanks for the information.
This is the SQL Query here:
SELECT NoteID, Time_Stamp, NoteDate, Line, Shift, OperatorNote
FROM tblOperatorNotes
WHERE (NoteDate >= @startDate) AND (NoteDate <= @enddate) and (Shift=@shift)
and Line in (@line)
order by Line

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

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

I've never had much luck in passing multi valued params to a stored procedure and in those cases I've just pulled the query back into the RDL instead of having it references a sproc.

I tried a number of string manipulations trying to add single quotes, commas etc And parse them out in the beginning of the stored procedure but eventually decided to just keep the query in the report.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Brad Allison Hall of Fame Points: 3550 More actions · Answer 2

Thanks for the reponse! I decided to do the same thing 🙂 Just keep it in the report itself.

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

See the following... it's a long article, but it's worth it.

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

http://www.sqlservercentral.com/articles/T-SQL/63003/

--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)