Thread: Dynamic Stored Procedure without Building Strings

Seems to work fine for me in SQL Server with NULL values. You'll probably just have to do a conditional in the procedure or use inline SQL.

If anyone is interested, this is how I worked it in MySQL. This version allows for NULL values in fields.

Code:

CREATE PROC [dbo].[usp_SearchCandidates]
candidateID int,
firstName varchar(25),
lastName varchar(25),
email varchar(100)
 
BEGIN
 
SET @candidateID=candidateID;
SET @firstName = firstName;
SET @lastName = lastName;
SET @email = email;
 
SELECT
    [candidateID],
    [firstName],
    [lastName],
    [email]
FROM
    [candidates]
WHERE (isActive = 1 AND
candidateID = IF(@candidateID=0,candidateID, @candidateID) AND
IF(ISNULL(@firstName),NOT ISNULL(firstName),firstName=@firstName) AND
IF(ISNULL(@lastName),NOT ISNULL(lastName),lastName=@lastName) AND
IF(ISNULL(@email),NOT ISNULL(email),email=@email)
)

This works where NULL values are used in field values. Obviously you can toggle the NOT ISNULL to just ISNULL, depending on what you want the Stored Procedure to return if you pass NULL as one of the parameters.

If you don't use NULL values in your fields, you could try this instead:-

Code:

candidateID = IF(@candidateID=0,candidateID, @candidateID) AND
firstName=IFNULL(@firstName,firstName) AND
lastName=IFNULL(@lastName),lastName) AND
email=IFNULL(@email,email)

Hope that helps someone.