Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Arrow Unanswered: Problem with creating stored procedure

    Hi,
    I've a problem with creating a tored procedure in MS SQL server.
    I need to create a query in which the condition i know only during runtime.
    suppose , i 've 5 fields in the table from which i am selecting,
    only during run time,I will know, on which columns the condition will be applied .
    Is it possible to create such type of Query?.
    Need of help...
    bye..
    durga.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Could you post an example? Will you have covering indexes on all columns?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    are you talking about something like this??

    Code:
    create table #tmp(col1 varchar(3), col2 varchar(3), col3 varchar(3), col4 varchar(3), col5 varchar(3))
    go
    insert into #Tmp values('A','B','C','D','E')
    insert into #Tmp values('E','D','C','B','A')
    insert into #Tmp values('A','C','E','D','B')
    insert into #Tmp values('D','B','A','C','E')
    go
    create procedure #test(
      @col1  varchar(3) = null
    , @col2  varchar(3) = null
    , @col3  varchar(3) = null
    , @col4  varchar(3) = null
    , @col5  varchar(3) = null)
    
    as
    
    select *
      from #tmp
     where (col1 = @col1 or @col1 is null)
       and (col2 = @col2 or @col2 is null)
       and (col3 = @col3 or @col3 is null)
       and (col4 = @col4 or @col4 is null)
       and (col5 = @col5 or @col5 is null)
    
    return 0
    go
    
    exec #test 'A'
    exec #test null,null,null,null,'E'
    exec #test @col5 = 'E'
    exec #test @col2 = 'B', @col4 = 'C'
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Problem with creating stored procedure

    What about using dynamic statements :

    EXECUTE
    sp_executesql

    declare @sql varchar(8000)
    set @sql='something created during executing of stored procedure'
    exe(@sql)

  5. #5
    Join Date
    Sep 2003
    Posts
    5

    Re: Problem with creating stored procedure

    Hi,
    Thanks..I got my result with Snail's hint.
    Actually
    My problem was i've to construct and execute a query whose condition is known only during runtime.
    And now i am able to create such thing with the help of Snail's hint.
    thankz friend..
    thankz a lot.
    bye
    by
    durga

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    dynamic sql...yikes!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Dynamic SQL is not as bad as, say, CURSORS!

    I've converted dynamic sql to direct sql and found the performance to be slower on some occasions. The cost of recompiling the procedure and not using cached plans was less than the cost of piling a lot of conditional statements into the query.

    One caveat: don't allow the user to submit a Where clause as a parameter. I did this once, and it was flexible and convenient, but then it was pointed out that somebody could slip an extra statement in like this:

    @WhereClause = "Where 1 = 1 go delete from MyMostImportantTable go" My bad.

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    oh yeah, sql injection alright

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •