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

Casey Crookston caseyc at IntelliSoftmn.com
Mon Aug 14 15:26:52 CDT 2006


CREATE proc PROCNAME
(
@param1 type(x)
@param2 type(x)
)

as
begin


declare @queryString varchar(8000)

if @param1 = 'xxxx'
    begin
       set @queryString = 'SELECT xxxxxx from yyyyyyy where fieldname = 
@param1'
    end

if @param2 = 'yyyy'
    begin
       set @queryString = 'SELECT zzzzzz from yyyyyyy  etc..... '
    end

exec(@queryString)

end

--------------------------

Okay, but this allows for only one search option to have been filed in.
Suppose the user has populated both parm1 and parm2?  Would this work:

CREATE proc PROCNAME
(
@param1 type(x)
@param2 type(x)
)

as
begin

declare @whereString varchar(8000)
declare @queryString varchar(8000)

if @param1 > ''
    begin
       set @whereString = 'xxxxxx LIKE @param1'
    end

if @param2 > ''
    begin
       set @whereString = @whereString  + 'AND zzzzzz LIKE @param2 '
    end

set @queryString = @queryString + @whereString

exec(@queryString)

end




More information about the thelist mailing list