Creating a Dynamic Where Clause in a stored procedure in SQL Server
I want a stored procedure in SQL Server to have two optional parameters, @CUSTOMERID and @ASSEMBLYID, and return a recordset. The procedure will only ever be called with just one of the two parameters specified. If the first one is specified I want the WHERE cause of the recordset to be "WHERE CustomerId = @CUSTOMERID", and if the second parameter is specified I want the WHERE clause to be "WHERE AssemblyId = @ASSEMBLYID". How can I dynamically create the where clause based on which parameter is provided? Can I save the SQL to a string variable and then somehow execute the string variable to generate and return the recordset?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Najam - Just wanted to know Why? What is the difference you found between the WHERE clauses you and I have posted?
For any customerid other than @CUSTOMERID where AssemblyId is null , that will be part of your result, which is not correct also other way around for AssemblyId
where (
(CustomerId = @CUSTOMERID OR @CUSTOMERID IS NULL)
AND
(ASSEMBLYID = @ASSEMBLYID OR @ASSEMBLYID IS NULL)
)will be the good one
Requirement doesn't state that. Let the author decide.
@Najam:
That is not true. A NULL value is never "=" another value, even another NULL. The first syntax is fine and much cleaner.
WHERE
CustomerId = @CUSTOMERID --a NULL CustomerID will NOT "=" a NULL @CUSTOMERID
OR
AssemblyId = @ASSEMBLYID --a NULL AssemblyId will NOT "=" a NULL @ASSEMBLYID
As noted, it could have performance issues being in the same proc, and therefore I stated that a recompile option ("hint") was also needed:
WHERE
CustomerId = @CUSTOMERID
OR
AssemblyId = @ASSEMBLYID
OPTION (RECOMPILE)
That is not true. A NULL value is never "=" another value, even another NULL. The first syntax is fine and much cleaner.
WHERE
CustomerId = @CUSTOMERID --a NULL CustomerID will NOT "=" a NULL @CUSTOMERID
OR
AssemblyId = @ASSEMBLYID --a NULL AssemblyId will NOT "=" a NULL @ASSEMBLYID
As noted, it could have performance issues being in the same proc, and therefore I stated that a recompile option ("hint") was also needed:
WHERE
CustomerId = @CUSTOMERID
OR
AssemblyId = @ASSEMBLYID
OPTION (RECOMPILE)
@ScottPletcher & @Arun Murugan - I agree, my bad.
ASKER
What do you think about this solution? Would I need an OPTION (RECOMPILE) if I did it this way?
DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT *.Assemblies FROM Assemblies WHERE '
IF @CUSOMTERID IS NOT NULL
SET @SQL = @SQL + 'CUSTOMERID = @CUSTOMERID '
ELSE
SET @SQL = @SQL + 'ASSEMBLYID = @ASSEMBLYID '
END
EXEC(@SQL)
No. And if you have more than two or three options, that is the preferred way to do it. Except you must also deal with potential SQL injection, such as by using sp_executesql and providing the value of @CUSTOMER|@ASSEMBLYID only as parameter values, not strung directly into the query as any kind of text.
ASKER
Thanks you.
Open in new window
However this kind of approach does not generate efficient query plan for both conditions, better split in two queries.
@Arun Murugan - Your query will give wrong results