CODEWITHSUNDEEP
sqlstored-procedures
pmillio
pmillio

Reputation: 1149

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

Upvotes: 1

Views: 2902

Answers (3)

sellmeadog
sellmeadog

Reputation: 7517

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.

Upvotes: 5

Kevin Burton
Kevin Burton

Reputation: 11924

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

Upvotes: 0

sll
sll

Reputation: 62554

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

Upvotes: 3

Related Questions