CODEWITHSUNDEEP
sql-serverstored-proceduresreporting
Flemming Hald
Flemming Hald

Reputation: 83

Use a report parameter instead of GETDATE in stored procedure

I have a SQL query which is saved as a stored procedure. I am using three different stored procedures to go back 1, 2, or 3 days depending on today's date. This makes it possible for my dashboard users to go back and skip weekends when comparing stats. This actually works. It looks at todays date and if it is Sunday go back 2 days, if it is Monday go back 3 days otherwise go back 1 day. The dashboard is created in Report Builder. Below is shown the between dates if today is Sunday (i.e. skip 2 days and go back to Friday which is the last normal working day). So much for the background. My challenge is now the user of the dashboard would like a BeginDate and EndDate date picker parameter in the report so I can no longer use these hard coded date strings but will have to incorporate a parameter into them. How do I replace a parameter say called @BeginDate in the following line where it says "GETDATE()":

BETWEEN DATEADD(DAY, -2, DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()), 0)) AND DATEADD(SECOND, -86401, DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()),0))

And based upon the date the end user selects then go and use one of the three stored procedures. I think I can make this work somehow, but I cannot figure out how to replace GETDATE() above with a parameter entered in by the user. If I just replace it with a parameter it throws back an error. I'm sure there is a really smart way of doing this. I'm still looking!!!

Upvotes: 0

Views: 716

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

If you are passing a new parameter into the stored prcoedure:

BETWEEN DATEADD(DAY, -2, @BEGINDATE) AND DATEADD(SECOND, -86401, @BEGINDATE)

Adding seconds seems unnecessarily complicated:

>= DATEADD(DAY, -2, @BEGINDATE) and XXX < DATEADD(DAY, -1, @BEGINDATE)

Upvotes: 1

Related Questions