What''s the best way of
-
Hi,
I have SP that will perform a search based on one parameter, but I never know in advance which one is going to be because the user has a list of searchable parameters to choose from.
Better than using words I'll type a simple example of what I'm used to do
CREATE PROCEDURE DBO.GETUSERS
@NAME NVARCHAR(250) = NULL,
@EMAIL VARCHAR(100) = NULL,
@PHONE VARCHAR(15) = NULL
AS
IF @NAME IS NOT NULL
BEGIN
-- RUN QUERY WITH WHERE CONDITION FOR NAME
END
IF @EMAIL IS NOT NULL
BEGIN
-- RUN QUERY WITH WHERE CONDITION FOR EMAIL
END
IF @PHONE IS NOT NULL
BEGIN
-- RUN QUERY WITH WHERE CONDITION FOR PHONE
END
The user can only pick one of those parameters.
I think this aproach is highly inefficient. Imagine if I have 50 parameters to pick from.
So my question is, what's the best way of aproaching a situation like this?
Thanks
-
See if this helps: http://www.sommarskog.se/dyn-search.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The best way is to follow the way explained in the link given by Frank Kails
Regards
Maths
-
ON THE PREMISE THAT
The user can ONLY pick ONE of those parameters.
I would re-create my query as follows: (parameter transparency, up to the sp to decide parameter object type)
CREATE PROCEDURE DBO.GETUSERS
@POLYMORP NVARCHAR(8000) = NULL
AS
--PSEUDO CODE
IF @POLYMORP CONTAINS '@'
I ASSUME THIS IS AN EMAIL - BUILD MY DYNAMIC SQL QUERY STRING HERE
ELSE IF LOWERCASE(@POLYMORP) = UPPERCASE(@POLYMORP)
I ASSUME THIS IS PHONE-NUMBER
ELSE IF LOWERCASE(@POLYMORP) <> UPPERCASE(@POLYMORP)
I ASSUME THIS TO BE A NAME
ETC...
ETC...
sp_executesql [@stmt =] stmt
--as referenced by Frank Kails
-
There is an easier way to do this w/o having to use Dynamic SQL.....
-
I agree with Tim. Why not do something like...
@NAME NVARCHAR(250) = '',
@EMAIL VARCHAR(100) = '',
@PHONE VARCHAR(15) = ''
SELECT *
FROM Users
WHERE (@Name = '' OR Name = @Name)
AND (@Email = '' OR Email = @Email)
AND (@Phone = '' OR Phone = @Phone)
It still can get lengthy with a lot of parameters, but it's one select.
-
Mark and Tim,
I believe you HAVE TO read the Link posted by Frank. It is not about dynamic sql is dynamic search. In there you can find both options as well as the good and bad things that you can expect from them
just my $0.02
* Noel -
I have what I believe is a better way of doing this.
I don't like to build strings and then exec the results, as there is the possibility of SQL injection, and also it doesn't compile.
Basically, when one of your searhc conditions is null, you want to exclude it from the filter
so in your case, something like the following will compile, and be resistant to SQL injection.
where NAME = COALESCE(@NAME ,NAME )
AND EMAIL = COALESCE(@EMAIL ,EMAIL )
AND PHONE = COALESCE(@PHONE, PHONE )
Hope this helps!!
Jonathan Starr
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply