StoreProc with multi select parameter

Question

Post reply

StoreProc with multi select parameter

Tara-1044200

SSCoach

Points: 15951
More actions
May 27, 2009 at 8:54 am

#137987

alter PROCEDURE [dbo].[ReportCounts]
@year char(4) =null,
@month char (3)= null,
@server char(5) =null,
@DB varchar(10)= null
AS
BEGIN
set nocount on
IF @DB<>''
select Col1,Col2,Col3,Col4,Col5
from DBcounts Col1=@db
order by MetricDate desc
IF @month <>''
select Col1,Col2,Col3,Col4,Col5
from DBcounts Col2=@month
order by MetricDate desc
IF @server <>''
select Col1,Col2,Col3,Col4,Col5
from DBcounts Col3=@server
order by MetricDate desc
IF @year <>''
select Col1,Col2,Col3,Col4,Col5
from DBcounts Col4=@year
order by MetricDate desc
IF (@year<>'' and @server <>''and @month<>'' and @PlanDB <>'')
select Col1,Col2,Col3,Col4,Col5
from DBcounts Where Col1=@db and Col2=@month and Col3=@server=@month and Col4=@year
order by MetricdDate desc
END
I am using the above proc for a ssrs report. I could select 1 parameter value and pull the data but how could i select multiple values for each parameter when executing the store proc.

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

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

WayneS SSC Guru Points: 95409 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