StoreProc with multi select parameter

Question

Post reply

StoreProc with multi select parameter

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

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

Check out these articles:

http://www.sqlservercentral.com/articles/Reporting+Services/62731/

http://www.sqlservercentral.com/articles/XML/63633/

Even though the first article shows one way, I would now do it by using the Join command to make a comma-delimited string, then using the method in the second article to take the comma-delimited string apart.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mverma4you Ten Centuries Points: 1013 More actions · Answer 2

you can use multi-select parameter in report but it will return you comma seperated value and you have to change the logic to handle the comma seperated value in the procedure and return the recordset based on selected value.

Tejas Shah Old Hand Points: 308 More actions · Answer 3

Hi,

You can write query as:

SELECT Col1, Col2, Col3, Col4

FROM table

WHERE (@db IS NULL OR Col1 = @Db)

AND (@month IS NULL OR Col2 = @month)

..

You can also use sp_executeSQL for the same.

Let me know if it helps you.

Thanks,

Tejas Shah

http://www.SQLYoga.com

Tejas Shah

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi,

try also this,

create PROCEDURE ReportCounts

@year char(4) =null,

@month char (3)= null,

@server char(5) =null,

@DB varchar(10)= null

AS

BEGIN

set nocount on

select Col1 A,Col2 B,Col3 C,Col4 D,Col5 E

from DBcounts Col1=@db and (@db '')

union --union all

select Col1,Col2,Col3,Col4,Col5

from DBcounts Col2=@month and (@month '')

union --union all

select Col1,Col2,Col3,Col4,Col5

from DBcounts Col3=@server and (@server '')

union --union all

select Col1,Col2,Col3,Col4,Col5

from DBcounts Col4=@year and (@year '')

set nocount off

END

ARUN SAS