April 12, 2019 at 3:14 pm
Hi Guys,
I have to create a montly SQL agent job for Stored Procedure where it can pick dates automatically. I have a stored procedure with two parameters @begindate and @enddate. As this is April, I want this Stored procedure to run for 3 months. Now I am hard coding this, but I want this to change automatically. If it is may, the Procedure should run for Feb, March, and April. Can I create like below
Exec rpt_MonthlyApprovalsProductivity 'Dateadd(month,datediff(month,0,getdate())-3,0)', 'EOMONTH(Dateadd(month,datediff(month,0,getdate())-3,0))'
Exec rpt_MonthlyApprovalsProductivity 'Dateadd(month,datediff(month,0,getdate())-2,0)', 'EOMONTH(Dateadd(month,datediff(month,0,getdate())-2,0))'
Exec rpt_MonthlyApprovalsProductivity 'Dateadd(month,datediff(month,0,getdate())-1,0)', 'EOMONTH(Dateadd(month,datediff(month,0,getdate())-1,0))'
Both parameters @BeginDate DATETIME
,@EndDate DATETIME
But this is giving me the error below :
Msg 8114, Level 16, State 5, Procedure rpt_MonthlyApprovalsProductivity, Line 557
Error converting data type varchar to datetime.
Msg 8114, Level 16, State 5, Procedure rpt_MonthlyApprovalsProductivity, Line 557
Error converting data type varchar to datetime.
Msg 8114, Level 16, State 5, Procedure rpt_MonthlyApprovalsProductivity, Line 557
Error converting data type varchar to datetime.
Exec usp_Storedprocedure '1/1/2019', '1/31/2019'
Exec usp_Storedprocedure '2/1/2019', '2/28/2019'
Exec usp_Storedprocedure '3/1/2019', '3/31/2019'
April 12, 2019 at 3:20 pm
How are the parameters for the procedure declared? Are they declared as VARCHAR(somevalue) or as DATETIME?
April 12, 2019 at 3:33 pm
@BeginDate DATETIME
,@EndDate DATETIME
April 12, 2019 at 3:33 pm
You can't pass expressions as parameter values. You need to resolve the expression yourself and pass only a single (scalar) value. For example:
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0))
EXEC dbo.rpt_MonthlyApprovalsProductivity @start_date, @end_date
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))
EXEC dbo.rpt_MonthlyApprovalsProductivity @start_date, @end_date
SET @start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
SET @end_date = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
EXEC dbo.rpt_MonthlyApprovalsProductivity @start_date, @end_date
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
April 12, 2019 at 3:44 pm
It gives me this error now If I declare and set values like above
Msg 134, Level 15, State 1, Line 582
The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure.
Msg 134, Level 15, State 1, Line 590
The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure.
April 12, 2019 at 3:48 pm
It gives me this error now If I declare and set values like above Msg 134, Level 15, State 1, Line 582 The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure. Msg 134, Level 15, State 1, Line 590 The variable name '@begindate' has already been declared. Variable names must be unique within a query batch or stored procedure.
Could you post the code?
April 12, 2019 at 3:50 pm
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-3,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-3,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-2,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-2,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-1,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-1,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
April 12, 2019 at 4:04 pm
I have to declare the variable only once then it will work.
April 12, 2019 at 8:53 pm
Declared variables only once, it worked. Thanks
declare @begindate datetime,
@enddate datetime
SET @begindate =Dateadd(month,datediff(month,0,getdate())-3,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-3,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
SET @begindate =Dateadd(month,datediff(month,0,getdate())-2,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-2,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
SET @begindate =Dateadd(month,datediff(month,0,getdate())-1,0)
SET @enddate=EOMONTH(Dateadd(month,datediff(month,0,getdate())-1,0))
Exec rpt_MonthlyApprovalsProductivity @begindate, @enddate
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply