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
returnHi,
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
