x

Multiple Valued Stored Procedure

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
more ▼

asked Jun 14, 2013 at 06:46 AM in Default

avatar image

kalpana.l
0 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I see you're trying to split @itmsgrp. If it is a comma delimited list of integers, the @itmsgrp parameter should be a string of some type: varchar(100) or something that meets your needs.

more ▼

answered Jun 14, 2013 at 04:17 PM

avatar image

KenJ
24.9k 3 10 19

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x687
x472
x93

asked: Jun 14, 2013 at 06:46 AM

Seen: 998 times

Last Updated: Jun 17, 2013 at 04:09 AM

Copyright 2017 Redgate Software. Privacy Policy