Forum Discussion

thedeeins's avatar
thedeeins
Copper Contributor
Jun 18, 2022

Parameterizing SQL Query

Hi All,

 

I am trying to implement parameter for my SQL query which will be executed using SQL Server agent Job on a scheduled basis. This parameter which sets the cutoff period (x) in days and job should load any changed records in last x days. On the other hand, if parameter is zero it should load everything.

 

Currently, I am having a where condition as follows:

 

.....

WHERE updatedate > case when x > 0 then dateadd(day, -x, getdate()) // for parameter eg; x = 2

                                    else getdate() - dateadd(day, -x, getdate()) // for parameter, x = 0

 

Is this logically correct to use or is there any better solution that anyone can suggest for me? 

 

Any help is much appreciated. 

 

Thank you!

1 Reply

  • Ronen_Ariely's avatar
    Ronen_Ariely
    MVP
    Jun 18, 2022

    Hithedeeins 

    You ask about query a table which we have no information about and we cannot read minds. Moreover, you try to describe something which a simple sample can be much more clear.

     

    please provide:

    1) Queries to CREATE your table(s) including indexes
    2) Queries to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Regards,