[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