April 19, 2007 at 9:20 am
Is there a way to use a variable, to change how a filter operates? Without using Dynamic SQL or having 2 seperate scripts. I'm thinking there is a boolean method for this.
The script is first run, Selecting all accounts, regardless of balance on the account. Then I manually un-comment the line "And total_balance = 0." to select ONLY accounts with balance = 0.
I know somewhere I used a condition that used a true/false variable that when used with the condition redered it one way or the other.
April 19, 2007 at 9:27 am
A common trick used is by using a flag in your parameters and something like this
AND (total_balance = IsNull(NullIf(@Balance,0),total_balance))
Which is @Balance = 0 (or is null even) then all records will be returned regardless or if @Balance has a value that value will be used.
Or of the issue is you only want those that have no balanace and your parameter is @NoBalance you might do
AND ( @NoBalance = 0 OR (@NoBalance = 1 AND total_balance = 0 )
could be other ways for you needs thou.
April 19, 2007 at 9:53 am
THATS IT!
Off to do some tests, but I knew I used it somewhere and I even mentioned 'Dynamic' SQL. It was an article right here on Stored Procedures and 'null' arguments.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply