CODEWITHSUNDEEP
sqlt-sqlwhere-clause
Chris Knight
Chris Knight

Reputation: 1476

SQL WHERE clause needs to be dynamic

I am working on a stored procedure to get accounts for a case. If the @accounts parameter is NULL, I want to get all the accounts for the case. If @accounts is NOT NULL, then it will be a comma separated string of accountid's. I would like to have a single where clause that can handle this. Something that says if @accounts is NULL then grab all the accounts for the case. Otherwise, use the @accounts parameter and grab the accounts with the account id's specified. I would like to avoid a big IF statement that would require me to have the query twice with 2 different WHERE clauses.

DECLARE @caseId BIGINT,
    DECLARE @accounts VARCHAR(255)

    SELECT TOP 1 @userId = userId FROM TblTraceCur t
    WHERE caseId = @caseid
    ORDER BY processDate DESC

    SELECT    
              .... (select logic) ...
    WHERE     
              t.caseId = @caseID AND 
              t.userId = @userId AND
              t.shortStock = 0 AND
              ... (where I need the new logic) ...
    order by t.tracln ASC

Thanks a lot!

Upvotes: 1

Views: 225

Answers (1)

Aust
Aust

Reputation: 11602

This worked for me. Add (@accounts IS NULL OR @accounts LIKE ('%,' + CAST(t.caseId AS VARCHAR(255)) + ',%')) where you indicated in your post. Of course you have to make sure that your first character and last character in @accounts are commas for this to work but it's the easiest I think. Not having the commas would return ids falsely for example: having @accounts = 12 would return ids 1, 2, and 12. Using commas and making sure they're the first and last characters prevent this from happening.

Upvotes: 3

Related Questions