Post reply

Passing variable parameter in SP

  • June 6, 2005 at 6:27 am

    #66555

    Hi,

    Require some help regarding the below scenerio -

    create table tbl1

    (a int,                                                          b int,

    c char(5))

    insert into tbl1 values(3,2,'abr')

    insert into tbl1 values(4,5,'ade')

    I want to create a proc which would display the values of table "tbl1" based on whatever parameter value is entered.

    like

    Exec test @d='a%' ,@e=3  should display '3 2 abr' only.

    or

    Exec test @d='a%' should display both the records.

    Any help would be very much appreciated.

    Thanks,

    kg


    KG

  • June 6, 2005 at 6:42 am

    #563171

    Maybe something like this

    DROP PROCEDURE usp_test

    GO

    CREATE PROCEDURE usp_test

     @a int = NULL,

     @b int = NULL,

     @c char(5) = NULL

    AS

    SELECT a,b,c

    FROM tbl1

    WHERE a = ISNULL(@a,a)

    AND b = ISNULL(@b,b)

    AND c LIKE RTRIM(ISNULL(@c,'%'))

    GO

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 7, 2005 at 7:45 am

    #563502

    Thanks david. This works.

    kg


    KG

  • June 7, 2005 at 8:14 am

    #563530

    This method will work, but will suffer from potential performance problems as the table gets larger.

    When a parameter is omitted, the query will evaluate each row of each column to make sure it matches itself.  With a large table, this gets expensive.

    Another way would be to build the query dynamically, adding only the needed tests to the where clause.  This method will add compilation overhead, however.  Once the cost of scanning indexes or tables exceeds the cost of the overhead, this method will yield better results than the "isnull" method.

    If extreme performance is required, you may need to use a conditional structure to execute the correct query for each possible combination of supplied parameters.  This can be very tedious, and requires 2^N queries, where N is the number of optional parameters.

    You'll need to evaluate this for your situation.  Sometimes ease of coding outweighs performance.

    hth

    jg

     

     

     

  • June 8, 2005 at 3:23 am

    #563758

    I agree with you Jeff. But in this case the number of permutations and combinations will keep on increasing in some queries having larger no. of parameters. So I wanted to avoid conditional statements.

    kg

     


    KG

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

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