Thread: Dynamic Stored Procedure without Building Strings

Today, I just learned a neat trick that allows you to have a "dynamic" stored procedure without building a query string and executing it.

For example, let's say we have the following table:

candidateID int
firstName varchar(25)
lastName varchar(25)
email varchar(100)
isActive bit

Typically, if I wanted to be able to search this table by any combination of candidate ID, First Name, Last Name or Email, I would have built a query like this:

Code:

CREATE PROC[dbo].[usp_SearchCandidates]
@candidateID int,
@firstName varchar(25),
@lastName varchar(25),
@email varchar(100)
 
AS
 
SET NO COUNT ON
 
DECLARE @sql varchar(600)
 
SELECT @sql = 'SELECT candidateID, firstName, lastName, email FROM candidates WHERE isActive = 1 '
 
IF (@candidateID IS NOT NULL)
    BEGIN
        SELECT @sql = @sql + ' AND candidateID = ' + @candidateID + ' '
    END
 
IF (@firstName IS NOT NULL)
    BEGIN
        SELECT @sql = @sql + ' AND firstName = ''' + REPLACE(@firstname,'''','''''') + ''' '
    END
IF (@lastName IS NOT NULL)
    BEGIN
        SELECT @sql = @sql + ' AND lastName = ''' + REPLACE(@lastname,'''','''''') + ''' '
    END
 
IF (@email IS NOT NULL)
    BEGIN
        SELECT @sql = @sql + ' AND email = ''' + REPLACE(@email,'''','''''') + ''' '
    END
 
EXEC(@sql)

However, today, my coworker showed me something that looks more clean and isn't "dynamically" built.

Code:

CREATE PROC [dbo].[usp_SearchCandidates]
@candidateID int,
@firstName varchar(25),
@lastName varchar(25),
@email varchar(100)
 
AS
 
SET NO COUNT ON
 
SELECT
    [candidateID],
    [firstName],
    [lastName],
    [email]
FROM
    [candidates]
WHERE (isActive = 1 AND
candidateID = ISNULL(@candidateID, candidateID) AND
firstName = ISNULL(@firstName, firstName) AND
lastName = ISNULL(@lastName, lastName) AND
email = ISNULL(@email, email))

For this to work, all parameters that aren't being utilized in the query should have a NULL value.

It took me 0:00:01 ms to run this query on a database with several thousand records. Maybe the database you tested it on isn't properly optimized

Originally Posted by lewy

You are looking for exact information, how about when you're looking for matches using LIKE?

That may be a different story. In many cases, it's not necessary to use LIKE. I guess I could have given a better example. I'm using a similar query where users select from several options like dates and specific values.

Regardless, this is providing an alternative to using dynamically built queries.

Originally Posted by lewy

btw - the new dbs I've been creating are very well optimized, though I can't say the same about the ones I inherited

Of course they are, lewy

I have not checked the cost of a query utilizing this type of process, but I have grown fond of using CASE statements in a where clause in order to allow for different parameter values. It may be "expensive" to run, but it is easier to read, understand and maintain. Also.. I almost never get all the single quotes right on the first try

I may have come up with a small problem with this.

Where a field has a NULL value and you don't want to include it in the WHERE clause, ie you set the parameter for that field to NULL, it doesn't seem to return the records where the value of that field is NULL.

I know some don't like to store NULL values in their DB, but I do, so is there a way round this?

btw, I'm using MySQL, so don't know if this is just an issue with it rather than MSSQL.

For MySQL, try IFNULL: MySQL :: MySQL 5.0 Reference Manual :: 11.3 Control Flow Functions

Originally Posted by jmurrayhead

For MySQL, try IFNULL: MySQL :: MySQL 5.0 Reference Manual :: 11.3 Control Flow Functions

Yep, I got that. The SP executes without any errors, but if any of the fields you're not checking the value of are NULL, then it doesn't return the record.
For example, if I had data like this

Code:

ID     forename      surname      email
1        Richy            Rich      NULL
2        Jason        Murrayhead    NULL

And had this SP

Code:

CREATE PROCEDURE GetUser(ID INT, email VARCHAR (50))
BEGIN
 
SET @ID = ID;
SET @email = email;
 
SELECT * FROM tblUsers
WHERE
ID=IFNULL(@ID,users.ID) AND
email=IFNULL(@email,users.email)
 
END

And I executed:

Code:

GetUser(NULL,NULL)

No records get returned.