June 6, 2012 at 10:23 pm
CREATE procedure Stray_shubham_test
@CNDateFrom varchar(100), @CNDateTo varchar(100), @Branch varchar(100),@NOofCNSFrom bigint,@NOofCNSTo bigint
AS
--Declare @CNDateFrom varchar(100), @CNDateTo varchar(100), @Branch varchar(100),@NOofCNSFrom bigint,@NOofCNSTo bigint
--set @CNDateFrom = '01-04-2012'
--set @CNDateTo = '30-04-2012'
--set @Branch = 'A123'
--set @NOofCNSFrom = 400
--set @NOofCNSTo = 500
set @CNDateFrom = dbo.getSqlFormatDate (isnull(@CNDateFrom,'01/01/1900'))
set @CNDateTo = dbo.getSqlFormatDate (isnull(@CNDateTo,'01/01/3000'))
Declare @Code1 as Varchar(20)
Declare @Value1 as Varchar(20)
Set @Value1 = SUBSTRING(cast(@Branch as varchar(20)),2, LEN(cast(@Branch as varchar(20))) -1)
Set @Code1 = SUBSTRING(cast(@Branch as varchar(20)),1, 1)
Set transaction isolation level read uncommitted
select BookingBranch,p.ContBranch as ContractingBranch,p.VBELN,Consignmentno,
ISNULL((Case when CNDate = '00000000' then NULL else CONVERT(date,Left(CNDate,4)+SUBSTRING(CNDate,5,2)+RIGHT(CNDate,2)) end),'3001-01-01') as CN_Date,
ISNULL((Case when p.PODAT = '00000000' then NULL else CONVERT(date,Left(p.PODAT,4)+SUBSTRING(p.PODAT,5,2)+RIGHT(p.PODAT,2)) end),'3001-01-01') as POD_Date,
LoadingCityName,DeliveryCityName,p.CustomerName,
sum(Round(BillingAmount,0)) as Freight,
sum(Round((isnull(shipmentcost,0) + isnull(RORF,0)),0)) as LorryHire
into #tmp
from SAP_FactTable_GM p
inner join SAPPROD03.DEP.dep.LIKP likp on likp.VBELN=p.VBELN
left join Cvzones_ProfitCenter_CostCenter_BP cv on cv.CostCenterId = p.BookingBranchid
--inner join #temp on #temp.CustomerId=p.CustomerId
inner join (Select cm.KUNNR as CustomerId,COUNT(VBELN) as NO_cns
from SAPPROD03.DEP.dep.VBPA spd
left join SAPPROD03.DEP.dep.KNA1 cm on cm.KUNNR = spd.KUNNR and cm.MANDT= '800'
where PARVW = 'AG'and spd.MANDT = '800'
group by cm.KUNNR having COUNT(*)>5)cust on cust.CustomerId=p.CustomerId
Where date between @CNDateFrom and @CNDateTo
and case when @Code1 = 'Z' then cv.ZoneID when @Code1 = 'R' then cv.RegionID when @Code1 = 'B'
then cv.BranchID else @Value1 end = @Value1
--and CustomerName in('CEMENT MANUFACTURING CO. LTD.- GUWAHATI','A.V.M SALES CORPORATION- KOLKATA')
Group by BookingBranch,p.ContBranch,p.VBELN,Consignmentno,
ISNULL((Case when CNDate = '00000000' then NULL else CONVERT(date,Left(CNDate,4)+SUBSTRING(CNDate,5,2)+RIGHT(CNDate,2)) end),'3001-01-01'),
ISNULL((Case when p.PODAT = '00000000' then NULL else CONVERT(date,Left(p.PODAT,4)+SUBSTRING(p.PODAT,5,2)+RIGHT(p.PODAT,2)) end),'3001-01-01'),
LoadingCityName,DeliveryCityName,p.CustomerName
Order by CustomerName
--select * from #tmp
select CustomerName,COUNT(*)NOofCNS into #tmp1 from #tmp
Group by CustomerName
having COUNT(*)> @NOofCNSFrom and COUNT(*)< @NOofCNSTo
select * --into #tmp5
from #tmp
where CustomerName in(Select distinct CustomerName from #tmp1)
Order by CustomerName
June 6, 2012 at 11:02 pm
June 7, 2012 at 4:17 am
Don't put the description of your issue in the subject, it gets truncated.
For those willing to help, this is the text hidden in the subject:
please any one help me, for finding error in that procedure,when i create a ssrs report from that procedure i can not find all the parameters from procedure at parameter pane
-- Gianluca Sartori
June 7, 2012 at 5:05 am
I expect the problem is temp tables, SSRS hits the same problem as SSIS in that it calls the sp initially with options set to just return the column definitions.
That fails when temp tables are created in the sp.
The article here http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/ descibes it in detail with respect to SSIS, but I believe apoplies equally to SSRS.
Mike John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply