Results 1 to 10 of 10

Thread: searching

  1. #1
    Join Date
    Mar 2004
    Posts
    80

    Unanswered: searching

    hi all,
    I need help in selecting records from a table based on the given search criteria.
    spec:
    select * from table where col1='x' and col2='y'... and col6='q'
    i may give any combination of column values.
    I mean I can't provide 6 values in 'where' condition all the time i submit query.
    help me with a stored proc which has 6 input parameters for the 6 columns.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One way to do it is like this:

    Where (col1=@Parameter1 or @Parameter1 is Null)
    and (col1=@Parameter1 or @Parameter1 is Null)
    .
    .
    and (col6=@Parameter6 or @Parameter6 is Null)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    or u can also use dynamic sql to generate the final search query.

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    thanks ,

    It worked.

    Originally posted by blindman
    One way to do it is like this:

    Where (col1=@Parameter1 or @Parameter1 is Null)
    and (col1=@Parameter1 or @Parameter1 is Null)
    .
    .
    and (col6=@Parameter6 or @Parameter6 is Null)

  5. #5
    Join Date
    Mar 2004
    Posts
    80
    how?
    any clues
    Originally posted by harshal_in
    or u can also use dynamic sql to generate the final search query.

  6. #6
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by theguru
    how?
    any clues
    some thing like this:



    create procedure search (
    @col1 int,
    @col2 int,
    @col3 int,
    @col4 int,
    @col5 int,
    @col6 int)
    begin
    declare @sql nvarchar(2000)
    set @sql ='select * from table '
    if (@col1 is not null or @col2 is not null or @col3 is not null @col4 is not null @col5 is not null or @col6 is not null )
    begin
    set @sql=@sql+' where '
    if @col1 is not null
    set @sql=@sql+ ' and col1='+@col1+

    if @col2 is not null
    set @sql=@sql+ ' and col2='+@col2+

    if @col3 is not null
    set @sql=@sql+ ' and col3='+@col3+

    if @col4 is not null
    set @sql=@sql+ ' and col4='+@col4

    if @col5 is not null
    set @sql=@sql+ ' and col5='+@col5

    if @col6 is not null
    set @sql=@sql+ ' and col6='+@col6

    exec sp_executesql @sql


    end

  7. #7
    Join Date
    Mar 2004
    Posts
    80
    thanks,
    I guess this is going to affect the performance badly when compared with the other query given by blindman specially when the stored proc needs to be executed repeatedly.what is your view?


    Originally posted by harshal_in
    some thing like this:



    create procedure search (
    @col1 int,
    @col2 int,
    @col3 int,
    @col4 int,
    @col5 int,
    @col6 int)
    begin
    declare @sql nvarchar(2000)
    set @sql ='select * from table '
    if (@col1 is not null or @col2 is not null or @col3 is not null @col4 is not null @col5 is not null or @col6 is not null )
    begin
    set @sql=@sql+' where '
    if @col1 is not null
    set @sql=@sql+ ' and col1='+@col1+

    if @col2 is not null
    set @sql=@sql+ ' and col2='+@col2+

    if @col3 is not null
    set @sql=@sql+ ' and col3='+@col3+

    if @col4 is not null
    set @sql=@sql+ ' and col4='+@col4

    if @col5 is not null
    set @sql=@sql+ ' and col5='+@col5

    if @col6 is not null
    set @sql=@sql+ ' and col6='+@col6

    exec sp_executesql @sql


    end

  8. #8
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by theguru
    thanks,
    I guess this is going to affect the performance badly when compared with the other query given by blindman specially when the stored proc needs to be executed repeatedly.what is your view?
    I agree with u set based approach is always the best.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My bet would be that the dynamic SQL would be significantly faster even though it is a bit harder to understand. In most cases if a usable index exists, it can avoid the table scan which would more than offset the compilation time.

    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Occasionally, dynamic SQL can be faster than a query containing multiple conditional clauses. Try them both and see.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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