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,