building a dynamic query

  • Thread starter Thread starter kmr
  • Start date Start date
K

kmr

I need to build a query that accepts input from a MS
Access Project - this works fine for one variable... but I
need to be able to accept multiple values for a field...
for example, I have variables called @animal1,
@animal2 ... @animal5
I need to make a query that says
where animal = @animal1 or animal = @animal2 or animal =
@animal3 if three were supplied - or
where animal = @animal1 or animal = @animal2 if two
variables were supplied -
I can't figure out how to construct the sql query if the
number of values is dynamic I tried using a basic If...
else with a begin and end but it appears you can't say
something like if (@animal1 is not null and @animal2 is
not null) begin ... end else if ...
Any suggestions/ideas greatly appreciated. thank you.

-kmr
 
k> I need to build a query that accepts input from a
k> MS Access Project - this works fine for one
k> variable... but I need to be able to accept
k> multiple values for a field... for example, I have
k> variables called @animal1, @animal2 ... @animal5
k> I need to make a query that says
k> where animal = @animal1 or animal = @animal2 or
k> animal = @animal3 if three were supplied - or
k> where animal = @animal1 or animal = @animal2 if
k> two variables were supplied -
k> I can't figure out how to construct the sql query
k> if the number of values is dynamic I tried using a
k> basic If... else with a begin and end but it
k> appears you can't say something like if (@animal1
k> is not null and @animal2 is not null) begin ... end
k> else if ... Any suggestions/ideas greatly
k> appreciated. thank you.

1. in your query, create some big number of parameters, more than you ever
expect to really be there; Then in the query put

....where (animal=isnull(@parm1,animal) or (animal=isnull(@parm2,animal)
or...

or


2. in your application, generate the comma-delimited string of @animals;
then in the query use sp_executesql. For instance, in query have

declare @s nvarchar(500)
set @s = 'select ... where animal in (' + @parm + ')'
sp_executesql @s




Vadim
 
Vadim Rapp said:
1. in your query, create some big number of parameters, more than you ever
expect to really be there; Then in the query put

...where (animal=isnull(@parm1,animal) or (animal=isnull(@parm2,animal)
or...
Click to expand...

You're missing some closing parentheses:

where (animal=isnull(@parm1,animal)) or (animal=isnull(@parm2,animal))
 
Back
Top