How to add a a parameter...

Question

Post reply

How to add a a parameter...

b_boy

SSCertifiable

Points: 5145
More actions
October 1, 2008 at 4:43 am

#199187

Hello I have this stored procedure writen like this:
USE [db]
GO
/****** Object: StoredProcedure [dbo].[usprunreport] Script Date: 10/01/2008 11:38:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspfruadreport]
as
declare @startdate datetime;
declare @enddate datetime;
set @enddate = convert(datetime, convert(varchar(11),getdate(),103),103);
set @startdate = dateadd(d,-5000,@enddate);
;with OrderDet (vOrderDate,vThreeMore, vAll, vNextDay, vAllOrders,DelivNotBill,vNextDayDelivery,vOver300,vNumOrdCC7Days)
as (select convert(datetime, convert(varchar(11),OrderDate,106),106) as vOrderDate
,sum(case when t.orderTotal >=300 then 1 else 0 end) as vThreeMore
,sum(1) as vAll
,sum(case when datediff(dd, t.OrderDate,t.ShipDate)<=1 then 1 else 0 end ) as vNextDay
,sum(1) as vAllOrders
,sum(case when (t.ShipAdd1 <> c.Add1)
OR
(t.ShipPostcode <> c.Postcode)
then 1 else 0 end
) as DelivNotBill
,sum(case when datediff(dd,t.OrderDate,t.ShipDate)<=1 then 1 else 0 end) as vNextDayDelivery
,sum(case when t.OrderTotal>300 then 1 else 0 end) as vOver300
,sum(isnull(p.Cnt,0)) as vNumOrdCC7Days
-- ,sum(p.Cnt) as vNumOrdCC7Days
from t_OrderHeader t
inner join t_Customers c
ON c.CustomerID = t.CustomerID
inner join t_Payments tp
on t.orderID = tp.OrderID
left outer join (select h1.orderID pOrderId, ccnumber pccnumber,
convert(datetime, convert(varchar(11),OrderDate,103),103) pOrderDate, count(*) cnt
from t_Payments p1 join t_orderHeader h1 on p1.orderid = h1.orderid where h1.OrderDate >=
dateadd(d,-5000,@endDate) group by h1.orderID , ccnumber , convert(datetime, convert(varchar(11),OrderDate,103),103)) p
on p.pOrderDate = convert(datetime, convert(varchar(11),OrderDate,103),103)
and p.porderId <> t.orderid
and p.pccnumber = tp.ccnumber
where OrderDate BETWEEN @startdate AND @enddate
group by convert(datetime, convert(varchar(11),OrderDate,106),106)
)
select * ,(CAST(vnextday AS FLOAT)/vallorders)*100 as '% Orders on next day delivery'
,(CAST(vThreeMore AS FLOAT)/vallorders)*100 as '% Orders over £300'
,(CAST(vNumOrdCC7Days AS FLOAT)/vallorders)*100 as '% Orders with same CC'
from OrderDet
order by vOrderDate desc
What I want to do is to be able to excute the stored proc as
exec usprunreport
within a date range to be created withn SSRS ...@startdate and @enddate

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

try this out

USE [db]

GO

/****** Object: StoredProcedure [dbo].[usprunreport] Script Date: 10/01/2008 11:38:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[uspfruadreport]

@startdate datetime,@enddate datetime

as

declare @startdate datetime;

declare @enddate datetime;

set @enddate = convert(datetime, convert(varchar(11),getdate(),103),103);

set @startdate = dateadd(d,-5000,@enddate);

;with OrderDet (vOrderDate,vThreeMore, vAll, vNextDay, vAllOrders,DelivNotBill,vNextDayDelivery,vOver300,vNumOrdCC7Days)

as (select convert(datetime, convert(varchar(11),OrderDate,106),106) as vOrderDate

,sum(case when t.orderTotal >=300 then 1 else 0 end) as vThreeMore

,sum(1) as vAll

,sum(case when datediff(dd, t.OrderDate,t.ShipDate)<=1 then 1 else 0 end ) as vNextDay

,sum(1) as vAllOrders

,sum(case when (t.ShipAdd1 <> c.Add1)

OR

(t.ShipPostcode <> c.Postcode)

then 1 else 0 end

as DelivNotBill

,sum(case when datediff(dd,t.OrderDate,t.ShipDate)<=1 then 1 else 0 end) as vNextDayDelivery

,sum(case when t.OrderTotal>300 then 1 else 0 end) as vOver300

,sum(isnull(p.Cnt,0)) as vNumOrdCC7Days

-- ,sum(p.Cnt) as vNumOrdCC7Days

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

b_boy SSCertifiable Points: 5145 More actions · Answer 2

Got the following errors:

Msg 134, Level 15, State 1, Procedure usprunreport, Line 5

The variable name '@startdate' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Procedure usprunreport, Line 6

The variable name '@enddate' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 102, Level 15, State 1, Procedure usprunreport, Line 21

Incorrect syntax near ','.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3