October 28, 2003 at 7:51 am
Please pardon the freshman question, but I am wondering about using an optional parameter in the WHERE clause of an SP.
Take this definition:
---------------------------------------------
CREATE PROCEDURE Test_WhereClause
@WhereClause varchar(500) = NULL,
WITH RECOMPILE
AS
Set NoCount ON
IF @WhereClause is NULL
SELECT * from Table WHERE Condition1 AND Condition2
ELSE
SELECT * from Table where Condition1 AND Condition2 AND @WhereClause
RETURN
---------------------------------------------
In the example above, Condition1 and Condition2 are always there. The thing what will change is the @WhereClause. In this case, it would be a properly formatted SQL string built on a user screen.
Of course T-SQL doesn't know that it will be properly formatted so it's complaining about it.
The idea seemed easy enough, but of course it doesn't work. Can some of you T-SQL vets give me a push in the right direction?
thanks in advance
October 28, 2003 at 8:12 am
This is not possible as you write it. Your solution is to use 'dynamic SQL'. Check BOL for 'EXEC'.
Your code would look something like :
CREATE PROCEDURE Test_WhereClause
@WhereClause varchar(500) = NULL
AS
DECLARE @sQuery varchar(8000)
Set NoCount ON
SET @sQuery = 'SELECT * from Table WHERE Condition1 AND Condition2'
IF @WhereClause not is NULL
SET @sQuery = @sQuery + ' AND ' + @WhereClause
EXEC @sQuery
RETURN
As always, be careful with this :
1. Performance is impacted, since the EXEC statement is NOT prepared
2. Be very, very careful with usersupplied where clauses. 'SQL Injection' problems are lurking in the background.
October 28, 2003 at 8:39 am
Thanks for the reply. I had just started looking into 'EXEC' before I came back to the board to check for replies.
It just so happens that this project is for a very small company and only 3 people will be using these SP's with the application.
But...I will keep your words of warning in mind for any future useage such as this.
thanks again.
October 28, 2003 at 8:45 am
Basically the same suggestion as NPeeters, but I would take a look at sp_executeSQL in BOL.
As for the security aspect, it doesn't matter if you have 3 or 3,000 users.
For some background information I suggtest reading http://www.algonet.se/~sommar/dynamic_sql.html
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 28, 2003 at 10:47 am
You can also use
Where
Col1 = @Param1
and (Col2 = @Param2 OR @Param2 Is Null)
and (Col3 = @Param3 OR @Param3 Is Null)
etc etc
When executing, Either Col2 will be = @Param2 or @Param2 is null. What ever the case, the statement is true.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
October 28, 2003 at 5:37 pm
I really like Crispin's way of doing it. The nice thing about his version is that depending on how often the parameter is null you can switch it around for better performance. 🙂
Where
Col1 = @Param1
and (@Param2 Is Null OR Col2 = @Param2) -- use when @Param2 is usually null
and (Col3 = @Param3 OR @Param3 Is Null) -- use when @Param3 usually has values
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 29, 2003 at 3:26 am
Likewise:
Where
Col1 = @Param1
and Col2 = COALESCE(@Param2, COl2)
and Col3 = COALESCE(@Param3, COl3)
etc etc
October 29, 2003 at 6:14 am
quote:
You can also useWhere
Col1 = @Param1
and (Col2 = @Param2 OR @Param2 Is Null)
and (Col3 = @Param3 OR @Param3 Is Null)
etc etc
When executing, Either Col2 will be = @Param2 or @Param2 is null. What ever the case, the statement is true.
Cheers,
Crispin
My experience has been that in an SP it is better to test for null outside of the statement rather than in the statement. The performance seems to be better, especially if there are many rows being tested.
For example:
if @param2 is null
where col1 = @param1
if @param2 is not null
where col1 = @param1 and col2 = @param2
John A. Kingsepp
HBS Corp
Director of Consulting Services
John A. Kingsepp
HBS Corp
Director of Consulting Services
October 29, 2003 at 6:38 am
I may be missing something, but if it is a small group program and you are obviously coding it, wouldn't the performance, security, etc be better if you implement the non-dynamic version as a separate SP?
Check for null in the client (I assume this is where the dynamic where is being generated) and call the appropriate SP or dynamic SQL SP. This could also be used to verify your dynamic where for SQL injection before posting it to the server.
Would anyone do this? I don't know -- I usually opt for easiest method which would be the single SP. However, I believe you would have a performance gain (in non-dynamic wheres) and a security gain doing it that way.
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC.
October 29, 2003 at 7:15 am
Not sure if your specific requirements and the approach I've used before mesh, but here's how I've used optional parameters in a WHERE clause before.
CREATE PROCEDURE spOptionalParam
(@Reqd int
,@Optnl varchar(255) = NULL)
AS
SET NOCOUNT ON
SELECT *
FROM TableName
WHERE ColumnA = @Reqd
AND CASE
WHEN @Optnl IS NULL THEN ColumnB
ELSE @Optnl
END = ColumnB
RETURN (@@ERROR)
When the caller of the procedure doesn't supply a value for the parameter, the SELECT statement is not restricted, when a value is supplied, then only rows matching the supplied value are returned.
The caveat here is that you can't use this procedure when attempting to return the rows where ColumnA IS NULL.
“In anything at all, perfection is finally attained not when there is no
longer anything to add, but when there is no longer anything to take away.”
Saint-Exupéry
Wind, Sand, and Stars
“In anything at all, perfection is finally attained not when there is no
longer anything to add, but when there is no longer anything to take away.”
Saint-Exupéry
Wind, Sand, and Stars
October 29, 2003 at 8:27 am
All those who posted after Frank need to read the article he referenced...
--Jonathan
--Jonathan
October 29, 2003 at 8:49 am
meaning?
October 29, 2003 at 12:39 pm
My experience is that the case statement is slow when the optional parameter is NULL, I converted all my sp's to use the dynamic sql EXEC, turned out to be 20tmes faster for me.
CASE
WHEN @Optnl IS NULL THEN ColumnB
ELSE @Optnl
END = ColumnB
October 30, 2003 at 5:30 am
quote:
My experience is that the case statement is slow when the optional parameter is NULL, I converted all my sp's to use the dynamic sql EXEC, turned out to be 20tmes faster for me.CASE
WHEN @Optnl IS NULL THEN ColumnB
ELSE @Optnl
END = ColumnB
What version of SQL?
I have run it on 7 (slow(ish)) but 2k was just as fast as excluding the null parts of the clause.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply