question

Hi all, Am using procedure in SSRS report which contains multiple valued parameter. If I pass multiple values for an integer parameter conversion problem occurs can anyone help me out in this?,Hi, Am using a stored procedure for SSRS report which contains multiple valued parameter. If I pass multiple values for integer parameter conversion problem occurs can anyone help me out in this? Below is my SP, create procedure test (@frmdate date,@todate date,@vendor nvarchar(100),@location integer,@itmsgrp integer) as declare @mrpst numeric(19,6),@mrpen numeric(19,6),@mrpact varchar(20),@openqty numeric(19,6), @openval numeric(19,6),@closeqty numeric(19,6),@closeval numeric(19,6),@soldqty numeric(19,6),@soldval numeric(19,6), @recqty numeric(19,6),@recval numeric(19,6) create table #turnTemp (MRP varchar(20), OpeningQty numeric(19,6), openValue numeric(19,6),Closeqty numeric(19,6),CloseVal numeric(19,6),soldqty numeric(19,6),soldval numeric(19,6),recqty numeric(19,6),recval numeric(19,6)) DECLARE CursorAging CURSOR FOR select startMRP,endmrp,mrp from turnTempMRP OPEN CursorAging FETCH NEXT FROM CursorAging into @mrpst,@mrpen,@mrpact WHILE @@FETCH_STATUS = 0 BEGIN with temp as ( select distinct a.ItemCode,U_mrp'mrp' from OSRi a left join oitm b on a.itemcode=b.itemcode left join OWHS c on a.whscode=c.whscode where b.ItmsGrpCod in (select value from dbo.split(@itmsgrp,',')) and c.Location in (select value from dbo.split(@location,',')) and --a.U_vendor collate database_default in (select value from dbo.split(@vendor,',')) and a.createdate<=@frmdate union all select distinct a.ItemCode,U_mrp'mrp' from OIBT a left join oitm b on a.itemcode=b.itemcode left join OWHS c on a.whscode=c.whscode where b.ItmsGrpCod in (select value from dbo.Split(@itmsgrp,',')) and c.Location in (select value from dbo.split(@location,',')) and --a.U_vendor collate database_default in (select value from dbo.split(@vendor,',')) and a.createdate<=@frmdate) , openingTemp as ( select t.*, case when isnull(MAX(p.Price),0) =0 then isnull(MAX(gn.Price),0) else isnull(MAX(p.Price),0) end CP,Sum(s.InQty-s.OutQty) openinQty from temp t left join PDN1 p on t.ItemCode collate database_default =p.ItemCode and p.DocDate <=@frmdate left outer join OIVL s on t.ItemCode collate database_default = s.ItemCode and s.DocDate <=@frmdate left outer join IGN1 gn on t.ItemCode collate database_default =gn.ItemCode and gn.DocDate <=@frmdate group by t.ItemCode,t.mrp) , ClosingTemp as ( select t.*, case when isnull(MAX(p.Price),0) =0 then isnull(MAX(gn.Price),0) else isnull(MAX(p.Price),0) end CP,SUM(s.InQty-s.OutQty) closinqty from temp t left join PDN1 p on t.ItemCode collate database_default =p.ItemCode and p.DocDate <=@todate left outer join OIVL s on t.ItemCode collate database_default = s.ItemCode and s.DocDate< =@todate left outer join IGN1 gn on t.ItemCode collate database_default =gn.ItemCode and gn.DocDate< =@todate group by t.ItemCode,t.mrp) , sold as( select distinct a.itemcode'SoldItem', isnull(SUM(outqty),0)-ISNULL(sum(inqty) ,0)'SoldQty',SUM(Price)'SoldPrice' from oivl a where docdate between @frmdate and @todate and TransType in(13,14) group by a.itemcode) ,rec as ( select distinct a.itemcode'Recitem',isnull(SUM(inqty),0)-isnull(sum(outqty),0)'RecQty',SUM(price)'RecVal' from oivl a where docdate between @frmdate and @todate and TransType in(18,20,21) group by a.itemcode) select @openqty=isnull(sum(o.openinQty),0),@openval=isnull(sum((o.openinQty*o.cp)),0),@closeqty=isnull(sum(c.closinqty),0),@closeval=isnull(sum((c.closinqty*c.CP)),0), @soldqty=isnull(sum(sr.SoldQty),0),@soldval=isnull(sum(sr.SoldPrice),0),@recqty=isnull(sum(rc.RecQty),0),@recval=isnull(sum(rc.RecVal),0) from openingTemp o left outer join ClosingTemp c on o.ItemCode=c.ItemCode and o.mrp=c.mrp left join sold sr on o.ItemCode=sr.SoldItem left join rec rc on o.itemcode=rc.Recitem where o.mrp between @mrpst and @mrpen insert into #turnTemp values(@mrpact,@openqty,@openval,@closeqty,@closeval,@soldqty,@soldval,@recqty,@recval) FETCH NEXT FROM CursorAging into @mrpst,@mrpen,@mrpact end close CursorAging deallocate CursorAging select * from #turnTemp drop table #turnTemp create FUNCTION [dbo].[Split] ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value int ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END