Problem with Select Statement

  • Hi all,

    I want to set where condition for the select statement only if the param for procedure is not null

    For example:

    CREATE PROCEDURE spg_Proc(@ID INT)

    AS

    BEGIN

    --SELECT Statement with where condition if @ID not null

    --SELECT Statement without where condition if @ID is null

    END

    I know we can check this with IF statement and do the desired operation, but i want to check the param is null or not within the SELECT statement itself, And add the condition to the select statement based on the param value.

    Thanks.

  • Hi,

    try this sp

    create procedure XYZ (@A int)

    as

    begin

    1) Select * from table

    where

    and @a is null or @a = ''

    2) Select * from table

    where

    and @a is not null or @a ''

    3) Select * from table

    where /*col1*/ = (case when (@A is null) or( @a = '') then Col1 else @a end)

    END

    ARUN SAS

  • Thanks arun.

    Your third option works for me.:-)

  • if IsNull(@ID,0) 0

    Select ... where col = @ID

    else

    Select ...

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • ezhil (4/30/2009)


    Thanks arun.

    Your third option works for me.:-)

    The third option can be re-written as

    SELECT * FROM table WHERE ( @a IS NULL OR Col1 = @a )

    [/CODE]

    --Ramesh


  • Notice that if your table is big, you'll might have performance issues. Unfortunetly I don’t remember the technical explanation on way using the third option causes a none optimal query plan (but I have a feeling that others will be able to give us a very good explanation). In any case take a look at the small demo that I wrote using AdventureWorks database. If you’ll compare both query plans and statistics I/O, you’ll see that when you use if statement, you’ll get a better query plan.

    create proc FindOrderDetail (@ProductID int)

    as

    select * from sales.SalesOrderDetail

    where (ProductID = @ProductID or @ProductID is null)

    go

    create proc FindOrderDetail2 (@ProductID int)

    as

    if @ProductID is null

    select * from sales.SalesOrderDetail

    else

    select * from sales.SalesOrderDetail where ProductID = @ProductID

    go

    set statistics io on

    exec FindOrderDetail 10

    exec FindOrderDetail2 10

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply