Multi Parameter Select query

Hi All,

I have a procdeure as written below.

I have created datasets in te report and in Report parameters clicked the Multi-value Parameter option.

When I run the report, I get all the customer names, when I select one customer report returns correct data. When I select two customers in the list box, the result set is empty. Can anyone guide me on what the error could be?

Thanks

Josh

Procedure:

create procedure MyMultiReport @.customername nvarchar(30), @.businessplantype nvarchar(30), @.businessplanyear nvarchar(10) as

Select PlanDatameta.sort,sysperiod.id,Planmeta.id,Planmonthlydata.Productmainpkey,Country, BusinessDivisiondescription, PlanSegmentPkey, Plantext.referencepkey, Plantext.usage, sheet, name, Plantext.text, Brand, Size, text1, PlanDatameta.sort+' '+Plantext1.text as LineDescription,line, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12, Total from Planmonthlydata join Plantext on Plantext.referencepkey=Planmonthlydata.Plansegmentpkey join PlanDatameta on PlanDatameta.pkey=Planmonthlydata.PlanDatametapkey join Productdescription on Productdescription.Productmainpkey=Planmonthlydata.Productmainpkey join Productmain on Productdescription.Productmainpkey=Productmain.pkey join Plansegment on Plansegment.pkey=Planmonthlydata.Plansegmentpkey join bpamain on bpamain.pkey=Plansegment.bpamainpkey join sysperiod on sysperiod.pkey=Plansegment.sysperiodpkey join Planmeta on Planmeta.pkey=Plansegment.Planmetapkey join Plantext Plantext1 on PlanDatameta.pkey=Plantext1.referencepkey where Planmonthlydata.status<>'d' and (PlanDatameta.sheet='PlanProductSummary') and Plantext.text<>'' and (PlanDatameta.line='MyPlanBaselineVolumeBasic' or PlanDatameta.line='BaselineVolumes' or PlanDatameta.line='IncrementalVolumes'or PlanDatameta.line='TotalVolumes') and name in (@.customername) order by PlanDatameta.sort,Plantext.text,text1

return

Hi,

If thecustomer name is the multi valued parameter you can't use that customer name @.CustomerName in the Where clause.

suppose Customer Name parameter contain these values:aaa, bbb , ccc then if you select the aaa, bbb from the parameter it will pass to the Stored procedure in the following format:

@.CustomeName='aaa,bbb'

When you selecting the one value from customer name parameter it is passing to the stored procedure like this: 'aaa' this when you used in In cluase will give you the result.

This @.CustomeName you can't directly use IN Cluase of where.

Select .. from Where name in('aaa,bbb') this will not give any result just you check by running the above select.

Instead you can do one thing

a)First create a table valued function like the following which will take the @.CustomerName as Input parameter and will return the table containg the

aaa

bbb splits the input string by comma and place in the Table.

ALTER function [dbo].[GetCSV]

(@.array varchar(max))

Returns @.t Table (Col1 varchar(max))

as

Begin

DECLARE @.separator_position INT

,@.array_value VARCHAR(1000)

,@.separator CHAR(1)

Set @.separator=','

--For my loop to work I need an extra separator at the end. I always look to the

-- left of the separator character for each array value

SET @.array = @.array + @.separator

-- patindex matches the a pattern against a string

WHILE PATINDEX('%' + @.separator + '%', @.array) <> 0

BEGIN

SELECT @.separator_position = PATINDEX('%' + @.separator + '%',@.array)

SELECT @.array_value = LEFT(@.array, @.separator_position - 1)

INSERT INTO @.t SELECT @.array_value

-- This replaces what we just processed with and empty string

SELECT @.array = STUFF(@.array, 1, @.separator_position, '')

END

Return

End

And use that resulted table in the where clause of your select statement

Select ..

from ..

Where name in (Select * from dbo.GetCSV(@.CustomerName))

It will give you the result.

Hope this helps.

Thanks

|||

You can also use dynamic SQL and filter the records and put them in a temporary table (#temp) first and use this query clause in main query:

code for dynamic SQL:

DELCARE @.strSQL VARCHAR(MAX)

CREATE TABLE #temp (name VARCHAR(50))

SET @.strSQL = 'SELECT name INTO #temp FROM [Table] WHERE name IN (' + @.customername + ')'

EXEC(@.strSQL)

code for main query:

AND name in (SELECT name FROM #temp1) AND ....

Also, dont forget to change the datatype of the input parameter @.customername to NVARCHAR(MAX).

Shyam