IF Statement in WHERE Clause

[david80]

I'm in the process of converting some of my ColdFusion Component SQL Queries to Stored Procedures. I have a SELECT statement with multiple IF Statements in the WHERE clause.

The ColdFusion SELECT Statement is as follows.

SELECT *
FROM Employees a, Departments b
WHERE a.EmployeeID=a.EmployeeID AND a.DepartmentID=b.ID

<CFIF #FirstName# IS NOT "">
AND FirstName LIKE '#FirstName#%'
</CFIF>

<CFIF #LastName# IS NOT "">
AND LastName LIKE '#LastName#%'
</CFIF>

<CFIF #Extension# IS NOT "">
AND Extension LIKE '#Extension#%'
</CFIF>

<CFIF #DepartmentID# IS NOT "">
AND DepartmentID = #DepartmentID#
</CFIF>

ORDER BY LastName, FirstName

The above text tells the query to run the additional AND's within the WHERE clause if the variable being passed is not null.

I've been struggling with a way to accomplish this within a Stored Procedure. Any help is greatly appreciated.

 

[ClaytonQ]

One way to make a Stored Procedure accept optional variables is by assigning default values to them when they are not passed. Something like:

CREATE PROCEDURE usp_Whatever
@EmployeeID smallint,
@FirstName nvarchar(25) = NULL,
@LastName nvarchar(25) = NULL

AS

IF @FirstName <> NULL
   BEGIN
     SELECT * FROM Employees a INNER JOIN Departments b
     ON a.DepartmentID = b.ID
     WHERE a.EmployeeID = @EmployeeID AND a.FirstName = @FirstName
   END

IF @LastName <> NULL
   BEGIN
     SELECT * FROM Employees a INNER JOIN Departments b
     ON a.DepartmentID = b.ID
     WHERE a.EmployeeID = @EmployeeID AND a.LastName = @FirstName
   END

And so on. I just wrote this off the top of my head, so I'm sure you'll have to debug it and expand on it, but I hope this sends you in the right direction. The repetetive parts of the SELECT statement can be put into a View and referenced.

Funny thing, I KNOW I wrote a stored procedure just like this not too long ago, but since I can't remember what it was for, I can't find it.

 

[ClaytonQ]

Found it! Maybe you can glean something useful from this. Names have been changed to protect the innocent:

CREATE PROCEDURE usp_MailingEntries
@Date nvarchar(20) = '%',
@First nvarchar(20) = '%',
@Last nvarchar(25) = '%'
AS
SELECT PersonID, FirstName, LastName, Address1, City, StateAbbrev, Zip, Phone, EmailAddress, DateEntered, Comments 
FROM People
WHERE DateEntered LIKE @Date
  AND FirstName LIKE @First
  AND LastName LIKE @Last

GO

The main difference between this and my earlier suggestion is the variables default to a wildcard (%) instead of to NULL.