Conditional @Parameter used in WHERE clause of SP

  • 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



    The ~BEST~ solution is always the simplest one!

  • 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.

  • 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.



    The ~BEST~ solution is always the simplest one!

  • 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

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • 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!

  • 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.

  • Likewise:

    Where

    Col1 = @Param1

    and Col2 = COALESCE(@Param2, COl2)

    and Col3 = COALESCE(@Param3, COl3)

    etc etc

  • quote:


    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


    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

  • 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.

  • 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.

    CREATEPROCEDURE spOptionalParam

    (@Reqdint

    ,@Optnlvarchar(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

  • All those who posted after Frank need to read the article he referenced...

    --Jonathan



    --Jonathan

  • meaning?

  • 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

  • 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