Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    14

    Unanswered: SQL Query condition

    Hi,

    I am creating a stored procedure which takes a few paramaters. One of which is a string of comma separated codes. At the end of my select query the codes will be used in the where clause.

    What I'm doing is parsing the comma separated string and populating a temp table called #codes. So my query will look something like this:

    select * from tableA
    where tableA.col1 = 'something'
    and tableA.code in (select * from #codes)
    and....


    However, the code paramater can be null and if this is the case I want the query to be called for all codes - i.e. I effectively want to remove the and tableA.code in (select * from #codes) part of the where clause.

    Is there any clever way of doin this other than having a if... else... and writing 2 queries - one with the and and one without it?

    Hope this is clear.

    Thanks,
    Wallace

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you are parsing the comma delimited string, then surely you can just use
    Code:
    WHERE tableA.code IN (@commadelimitedstring)
    Which will cut down on some complexity and overhead.
    Also, it then allows you to do this
    Code:
    WHERE tableA.code IN (@commadelimitedstring)
    OR    @commadelimitedstring IS NULL
    Which means that if the variable is omitted then the OR condition will validate to true returning all values.

    George
    Home | Blog

  3. #3
    Join Date
    Oct 2007
    Posts
    14
    Thanks for the response, but I'm not sure if I understand correctly. The parameter will be passed in so will look something like this:

    @param1 = 'codeA,codeB,codeC,codeD'

    I will then take the string and create a temp table (#codes) which in this case will have 4 rows. If I just do where tableA.code IN (@param1) no results are returned but if I do where tableA.code IN (select * from #codes) I get results.

    So, param1 can be null and if it is then I want the results for all codes returned - so there might also be codeE, codeF, codeG so the query would return answers for codeA - codeG.

    If I leave the clause where tableA.code (select * from #codes) in the query, #codes will be an empty table and therefore no rows will be returned? I need a way of eliminating this clause when #codes has no rows.

    I think this should have cleared up my question.

    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How are you parsing the string into the table?

    Anyhow, keep it as you are so far and add the following
    Code:
    OR    @param1 IS NULL
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2007
    Posts
    14
    Here is my code for parsing the parameter:

    CREATE TABLE #code_list ( code char(12) null )

    SELECT @pos_of_comma = charindex(",",@codes),
    @str_ctr = 1,
    @str_len = datalength(@codes),
    @new_str = NULL
    IF (@pos_of_comma > 0 )
    BEGIN
    WHILE ( @str_ctr <= @str_len )
    BEGIN
    SELECT @cur_char = substring(@codes,@str_ctr,1)
    IF ( @cur_char = ',')
    BEGIN
    INSERT #code_list (code)
    VALUES ( ltrim(rtrim(@new_str)) )
    SELECT @new_str = NULL
    SELECT @str_ctr = @str_ctr+1
    CONTINUE
    END
    SELECT @new_str = @new_str+@cur_char
    SELECT @str_ctr = @str_ctr + 1
    END
    INSERT #code_list(code) VALUES(ltrim(rtrim(@new_str)) )
    END
    ELSE
    BEGIN
    INSERT #code_list(code) VALUES(ltrim(rtrim(@codes)) )
    END

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    select * from tableA
    where (tableA.col1 = 'something'
    and tableA.code in (select * from #codes)
    and....
    ...)
    OR @param1 IS NULL
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2007
    Posts
    14
    That seems to have done the trick .

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •