IF Statements in Stored Procedures

[romantictiger]

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:

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:

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,

[romantictiger]

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:

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,

[andi_g69]

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

[kubeld]

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.

[rwendel]

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?

[romantictiger]

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,

[romantictiger]

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?)

[YuckFou]

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