[thelist] To Store or Not to Store - Building dynamic queries

Brian Cummiskey brian at hondaswap.com
Mon Aug 14 16:09:53 CDT 2006


Casey Crookston wrote:
> Brian - thanks for your help.  I wasn't too clear on what I was really
> asking:  Will this kind of syntax work in a stored proc?
>
> if @param1 > ''
>
> set @queryString = 'SELECT * FROM table WHERE ' + @whereString
>
> Thanks!
>
> Casey
>
>   
No, you can't do math on a string like that.  (greater than space?)


If you have something like:

search: <select name="srchtype"><option 
value="books">Books</option><option value="cd">CDs</option></select>
for: <select name="srcw"><option value="title">Title</option><option 
value="author">Author</option></select>
<input type="text" name="textfield" />

You could do something like this:

pass srchtype and srcw in as a parameter to the SP (this will depend on 
your scripting language)

then in the SP,

set @queryString = 'SELECT * FROM '


if @srchtype = 'books'
    begin       
        set @queryString = @queryString + 'BOOKSTABLE'
             if @srcq = 'title'
                begin
                   set @queryString = @queryString + ' WHERE title = ' 
@textfield
                  else
                     set @queryString = @queryString + ' WHERE author = 
' @textfield
                end
          end
       end
end


That's just quick and probably missing some quotes in the syntax, but i 
think you get the idea.





More information about the thelist mailing list