Results 1 to 8 of 8

Thread: IF Statements in Stored Procedures

  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Question IF Statements in Stored Procedures

    Ok...here's the deal. Two tables: Photo, Category. I have a stored procedure that lists all photos. What I would like is to pass a parameter to the procedure (optional) that would allow me to filter the list. So basically, if the parameter isn't specified, return all photos. If it is specified, return only photos that belong to a certain group.

    I have syntax that will work:
    Code:
    CREATE PROCEDURE [dbo].[INN_getPhotos]
    (
       @categoryID   int = NULL
    )
    AS
    
    IF @categoryID IS NULL
       BEGIN
          SELECT
             photo_id,
             photo_name,
             photo_thumb_name,
             photo_caption,
             photo_byte_size,
             active_photo,
             category_id
    
          FROM
             [Photo]
       END
    
    ELSE
       BEGIN
          SELECT
             photo_id,
             photo_name,
             photo_thumb_name,
             photo_caption,
             photo_byte_size,
             active_photo,
             category_id
    
          FROM
             [Photo]
    
          WHERE
             category_id = @categoryID
       END
    GO
    I don't like this solution. What I would like to do is this:
    Code:
    CREATE PROCEDURE [dbo].[INN_getPhotos]
    (
    	@categoryID	int	= NULL
    )
    AS
    
    SELECT
       photo_id,
       photo_name,
       photo_thumb_name,
       photo_caption,
       photo_byte_size,
       active_photo,
       category_id
    
    FROM
       [Photo]
    
    IF NOT @categoryID IS NULL
       WHERE category_id = @categoryID
    GO
    As you can see, the second option is shorter, cleaner, and more elegant. However, it also is invalid!

    Is there a way to do this? (Of course, I could build a string containing the SQL statement, and then do something like EXEC(strSQL), but I find that to be confusing...especially when you have to build a pretty long SQL statement with WHERE clauses, AND clauses, ORDER BY clauses, etc... It simply isn't elegant.

    So, can anyone help me out? I'm sure there's gotta be a way to do what I desire, but I just don't know the correct syntax.

    Thanks in advance,
    Last edited by romantictiger; 03-15-2003 at 01:56 AM.

  2. #2
    Join Date
    Mar 2003
    Posts
    7
    Well, I played a little harder and found a solution that works. It requires me to build a string (sSQL), but I can format this string in an interesting way. Check this out:

    Code:
    CREATE PROCEDURE [dbo].[INN_getPhotos]
    (
       @categoryID   VARCHAR(100) = NULL
    )
    AS
    
    DECLARE @sSQL   VARCHAR(4000)
    
    -- Begin constucting SQL string
    SET @sSQL = "
       SELECT 
          photo_id,
          photo_name,
          photo_thumb_name,
          photo_caption,
          photo_byte_size,
          active_photo,
          category_id
       FROM
          [Photo]
    "
    
    -- If a parameter has been passed for categoryID, then append a WHERE clause onto the SQL string
    IF NOT @categoryID IS NULL
       BEGIN
          SET @sSQL = @sSQL + "
             WHERE 
                category_id  = '" + @categoryID + "'"
       END
    
    
    -- Execute the SQL string
    EXEC(@sSQL)
    
    GO
    I'm not sure why I'm allowed to break a string onto multiple lines without closing it and using a concatenator, but I'll take it...!!! This way, I can easily comment out a field if I don't want to include it in the query (which I find invaluable).

    One thing that I'm kinda upset about, is the declaration of the @categoryID. It appears that I must declare it as a varchar instead of an int! See if you can figure this one out:

    If I declare it as an int, I can save the procedure, and even running a syntax check on it is fine. EVEN if I drop to Query Analyzer and run the procedure without passing parameter, it runs fine. But as soon as a pass a parameter, it bombs!! (saying it can't convert the varchar value to a column of data type int).

    Now, what's weird is that the 'varchar' it's talking about is the ENTIRE SQL statement, and not just my parameter. Query Analyzer will actually display my query in its pane window, along with the error. And what does it show as the value of my parameter??? It's an empty string. What's with that? I specifically passed in '1' as the value (with and without quotes...just to make sure...as yes, 1 would be a valid value for categoryID).

    So, I'm at a loss as to what to do, but I know that if I simply change the declaration to be VARCHAR instead of int, the procedure runs like a charm!

    I absolutely hate programming by coincedence, but at this point, what should I do? Any clues?

    Thanks in advance,
    Last edited by romantictiger; 03-15-2003 at 03:15 AM.

  3. #3
    Join Date
    Nov 2002
    Location
    DE
    Posts
    246
    The section:

    SET @sSQL = @sSQL + "
    WHERE
    category_id = '" + @categoryID + "'"

    errors out because SQL tries to do a mathematical '+' rather than a string concatenation if @categorID is defined as int.
    The reason why you do not get an error in the design view of your SP is that the syntax of the statement is correct (and that's what SQL is checking). Your error occurs at runtime when it tries to add the string and the int variable.
    Use an explicit convert:
    SET @sSQL = @sSQL + "
    WHERE
    category_id = '" + CAST (@categoryID as varchar) + "'"

    You might approach the problem in a complete different manner:

    CREATE PROCEDURE [dbo].[INN_getPhotos]
    (
    @categoryID int = NULL
    )
    AS

    SELECT
    photo_id,
    photo_name,
    photo_thumb_name,
    photo_caption,
    photo_byte_size,
    active_photo,
    category_id

    FROM
    [Photo]

    WHERE
    (category_id = @categoryID) or (@categoryID IS NULL)


    In this case you do not need the IF statement and you do no need to uild the SQL dynamically

  4. #4
    Join Date
    Mar 2003
    Location
    UK
    Posts
    2
    You have veered from your initial target, interesting though it was. A simple resolution to allow the SELECT statment to be authored only once (best programming practice), is to use the NVL() function in the WHERE clause associated with the LIKE operand.

    WHERE category_id LIKE NVL(@categoryID, '%')

    The NVL will return the value of @categoryID if this is not NULL, or if it is NULL then it will return a '%' sign. The LIKE operand will now enforce either a match only to the value of @categoryID, or to everthing in the table.

  5. #5
    Join Date
    Mar 2003
    Location
    Jacksonville, Florida
    Posts
    52
    andi_g69 posted:

    WHERE
    (category_id = @categoryID) or (@categoryID IS NULL)

    My question is, would execution be slightly faster (really immeasurable actually) if it read:

    WHERE
    (@categoryID IS NULL) or
    (category_id = @categoryID)

    Because I know that with like C or PHP if you do an 'or' that if the first condition is met, it does not even check the second...is this M$'s T-SQL this intuitive?

  6. #6
    Join Date
    Mar 2003
    Posts
    7

    Thanks...

    kubeld,

    Yes, I know that I veered from my original intention, but I was at a loss as to the most elegant solution.

    And then you came along...

    Thanks for the idea, and I'll definitely be rewriting my procedure.

    Thanks again,

  7. #7
    Join Date
    Mar 2003
    Posts
    7

    NVL function in SQL2000

    kubeld,

    It appears that the NVL function does not exist in SQL2000 (perhaps why I haven't heard of it).

    Any clue if Microsoft revised that function and names it something else? I'm assuming that it's the ISNULL() function...which is what I'm about to use.

    But for knowledge, what did the NVL stand for? (Null Value L...? or something to that effect?)
    Last edited by romantictiger; 03-23-2003 at 05:47 PM.

  8. #8
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152

    Talking

    andi_g69 has right...
    also you can use "case statement" in where clausula:
    case when @val is null then col else @val End

    CREATE PROCEDURE [dbo].[INN_getPhotos]
    (
    @categoryID int = NULL
    )
    AS

    SELECT
    photo_id,
    photo_name,
    photo_thumb_name,
    photo_caption,
    photo_byte_size,
    active_photo,
    category_id

    FROM
    [Photo]

    WHERE
    category_id = case when @categoryID is null then category_id else @categoryID End
    GO
    You Have To Be Happy With What You Have To Be Happy With (KC)

Posting Permissions

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