1

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!

Chris Knight
  • 1,448
  • 1
  • 15
  • 21
  • 1
    You will be better off using temporary table and [Split() function](http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql) because you will allow query optimizer to use indexes thus improving speed. – Nikola Markovinović Jul 26 '12 at 07:13

1 Answers1

3

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.

Aust
  • 11,552
  • 13
  • 44
  • 74