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,