Writing a dynamic where clause in a stored procedure

Hi i have a stored procedure which will have a dynamic clause, this is what my stored procedure looks like;

SELECT  Comment.AddDate,Event.StartTime,Users.Name
FROM Comment
JOIN 开发者_如何学JAVAUsers on Users.USERS_ID  = Comment.UserID
JOIN Event ON Users.USERS_ID = Event.UserID

If i pass a variable @Name the stored procedure should then look like this;

SELECT  Comment.AddDate,Event.StartTime,Users.Name
FROM Comment
JOIN Users on Users.USERS_ID  = Comment.UserID
JOIN Event ON Users.USERS_ID = Event.UserID
WHERE Name = @Name

However there are cases whereby the @Name i pass in will be empty, which means i want everything returned and not based on a specific name.

Also in some cases i will also pass in an eventid, how do i go about doing this?

You can use the COALESCE function in your where clause to use either the variable or the existing value, something like:

WHERE Name = COALESCE(NULLIF(@Name, ''), Name) AND EventID = COALESCE(@EventID, EventID)

UPDATE:

I added the NULLIF function within the COALESCE which says that if the value of @Name is an empty string, treat it as NULL therefore allowing the COALESCE function to work properly.

WHERE (@Name IS NULL OR Name = @Name)

no need for dynamic sql, just try:

SELECT  Comment.AddDate,Event.StartTime,Users.Name
FROM Comment
JOIN Users on Users.USERS_ID  = Comment.UserID
JOIN Event ON Users.USERS_ID = Event.UserID
WHERE (@Name IS NULL OR Name = @Name)
OR (@Id IS NULL OR Id=@Id) 

The @var IS NULL should short circuit the column = @var predicate if it is not supplied, this means you can supply either parameter to select your rows