June 17, 2003 at 5:50 am
I would like to select customers from my database depending on some criteria that the users choose - such as area code, company size etc. Since i have about 15 criteria and the users want to be able to choose many criteria for one selection i can't make one procedure for each criteria - i would end up with about a hundred procedures. I want to let the users choose what criteria to search on and have ONE single select statement that return the customers. Something like:
select name, address, city etc
from customer
where if users want to see customers of a certain type then type = @myInParameterForType
and if users want to see customers from a certain area then area = @myInParameterForArea
is this possible? sorry if i made you all confused...my english is not perfect! Thanks in advance!
June 17, 2003 at 5:55 am
The easiest way is to build the statement and execute at run time. If you want to do in a procedure, you'd do something like this:
@sql='select * from table where ' + @param1 + '=' + @param2
sp_executesql (@sql)
It's often easier to do it client side using application code. Lot's of content here on the site about dynamic sql will show you some more advanced options.
Andy
June 17, 2003 at 6:09 am
Hmm. Maybe i have to learn dynamic sql at some point...But would it be possible to add some kind of "flag" that says whether the user is interested in the specific criteria or not (e.g 1 if yes, 0 if no), and then in the where clause use only those criteria whose flag says 1??? Say that i add an extra param. for each criteria - like @area, @areaFlag, @type, @typeFlag - would that work? If so, how?
June 17, 2003 at 6:13 am
Another way would be to use the OR method (Better name?)
Select * From xx Where
(Col1 = @param1 OR @Param1 Is Null)
and (Col2 = @param2 OR @Param2 Is Null)
If any of the params are not supplied, they are null so the statement is still true.
There was another thread a week or so ago which went indepth with this topic. I can't for the life of me find it.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
June 17, 2003 at 6:13 am
Hi elisabet,
quote:
Hmm. Maybe i have to learn dynamic sql at some point...But would it be possible to add some kind of "flag" that says whether the user is interested in the specific criteria or not (e.g 1 if yes, 0 if no), and then in the where clause use only those criteria whose flag says 1??? Say that i add an extra param. for each criteria - like @area, @areaFlag, @type, @typeFlag - would that work? If so, how?
you mean something like
CREATE PROCEDURE dbo.fai_getMailVaryingSubjects_Attach @sender_name varchar(100), @subject varchar(150), @iMode int AS
IF @iMode = 1
BEGIN
SELECT TOP 200
MsgSubject
AS
Titel, ID
FROM
mails_header
WHERE
((MsgOrigDisplayName= +@sender_name) AND (MsgSubject LIKE @subject))
ORDER BY
MsgDateReceived
DESC
END
ELSE
SELECT
MsgSubject
AS
Titel, ID
FROM
mails_header
WHERE
((MsgOrigDisplayName= +@sender_name) AND (MsgSubject LIKE @subject))
ORDER BY
MsgDateReceived
DESC
GO
???
Oops, the formatting has gone!
Cheers,
Frank
Edited by - a5xo3z1 on 06/17/2003 06:15:07 AM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 17, 2003 at 6:25 am
Thanks guys...
used Crappy's code (feels weird to call someone who helped me crappy but oh well) and it worked perfectly well!
/Lis
June 18, 2003 at 2:32 am
I have a UDF that allows us to select a single value or a range. It does away with the need for executing dynamic strings. It only works for numeric PK's (because that's all I needed) but I'm sure it could be adapted to fit other scenarios.
It's very simple but means that we can run sp's as complete statements rather than compiling and executing on the fly.
CREATE FUNCTION LastPark
(@park int)
RETURNS int
As
Begin
/*********************************************************************************
Description:
This function returns the last park in the parks table (or view) if the user
passes a zero. If the user passes any other value that value is returned.
Purpose:
For a number of the reports generated from the data in this database
the user can select a single park or all parks (amongst other criteria).
This function allows the developer to find the ref of last park and so
run queries as BETWEEN @park AND dbo.LastParks(@park). This
has the effect of allowing the same criteria regardless of whether a
single park or all parks have been selected.
Parameters:
In:
@park int -An integer representing the park selected by the user
Out:
@endpark int - An integer representing either the highest ref in the
park table or the same value as @park.
Usage:ParkRef BETWEEN @park AND dbo.LastPark(@park)
History:
Written By:Tim Foster - System 24 Ltd
Create Date:8 April 2003
Version:1.0
*********************************************************************************/
DECLARE
@endpark int
If @park = 0
Begin
SELECT @endpark = Max(Ref) FROM Park
End
Else
Begin
SELECT @endpark = @park
End
RETURN @endpark
End
June 18, 2003 at 7:02 am
Another way to handle this is with a simple case statement in the WHERE clause. We use this alot as most of our sp's run with multiple, non-required paramenters. We have procs with 15-20 parms and it works fine.
Example proc:
create test_proc
@parm1 int=0,
@parm2 varchar(10)=null,
@parm3 varchar(20)=null
as
select
field1,
field2,
field99
from
tablex
where
1 = case
when @parm1 = field1 then 1
when @parm1 is null then 1
else 0
end
and 1 = case
when @parm2 = field2 then 1
when @parm2 is null then 1
else 0
end
and 1 = case
when @parm3 = field99 then 1
when @parm3 is null then 1
else 0
end
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply