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