handling parameters that could be missing

I have a search app that I'm developing in ASP.NET
It has 5 parameters
MRN, Name, Sex, DOB, SSN
All or any combination can be used.
what is the best way to handle the non-supplied parameters?
i.e. if the user selects only supplies 'Name' and 'MRN'
I want the SQL for the stored procedure to read as if it were just 2
parameters:
(but have one stored procedure that has parameter holders for all 5)
Select MRN, Name, Sex, DOB, SSN
From Membership
WHERE MRN = 123456 AND Name like 'Johns%'
The way I have been handling this is to rewrite a different SQL statement
for every possible situation, and pass that string to the adapter. But I ca
n
see that I'm not taking advantage of the processing that SQL server could do
in a stored procedure, and for 1.4 Million records, my app is just timing ou
t
all the time.
This must be a common issue. Could someone show me the ideal way to handle
this?Have a look at
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jonefer" <jonefer@.discussions.microsoft.com> wrote in message
news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
> can
> see that I'm not taking advantage of the processing that SQL server could
> do
> in a stored procedure, and for 1.4 Million records, my app is just timing
> out
> all the time.
> This must be a common issue. Could someone show me the ideal way to
> handle
> this?
>|||"jonefer" wrote:
> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
Have you tried something along the line of:
create procedure p_MyProc
@.pMyParm1 int = NULL, @.pMyParm2 datetime = NULL,
@.pMyParm3 varchar(128) = NULL, @.pMyParm4 bit = NULL
as
if @.pMyParm1 IS NULL do something ...
if @.pMyParm2 IS NULL do something ...
if @.pMyParm3 IS NULL do something ...
if @.pMyParm4 IS NULL do something ...|||Thank you. That was excellent.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||I implemented my search with the 5 parameters using your Dynamic SQL example
.
But because of the way I want to present the final results... it always
times out.
So I will list what I'm trying to get in my final results as well as what
the Stored procedure looks like (perhaps you can help me tweak it)
What I want in the final selection:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;
How I implemented this in the Stored Procedure:
CREATE PROCEDURE qMemberSelect
@.MRN int = NULL,
@.MemNAME nvarchar(40) = NULL,
@.DOB datetime = NULL,
@.SSN nvarchar(9) = NULL,
@.SEX nvarchar(1) = NULL,
@.debug bit = 0 AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql =
'SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP
],[SGR],[FROM-DT],[THRU-DT] FROM
MEMBERSHIP AS Y
WHERE 1=1'
if @.MRN IS NOT NULL
SELECT @.sql = @.sql + ' AND MRN = @.xMRN'
if @.MemNAME IS NOT NULL
SELECT @.sql = @.sql + ' AND MemName like @.xMemNAME + ''%'''
if @.DOB IS NOT NULL
SELECT @.sql = @.sql + ' AND DOB = @.xDOB'
if @.SSN IS NOT NULL
SELECT @.sql = @.sql + ' AND SSN = @.xSSN'
if @.SEX IS NOT NULL
SELECT @.sql = @.sql + ' AND SEX = @.xSEX'
if @.debug = 1
PRINT @.sql
SELECT @.sql = @.sql + ' GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR,
[FROM-DT], [THRU-DT]
HAVING len([THRU-DT])= 0
Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
Membership As X Where X.MRN = Y.MRN And X.MRN
Not in(Select Z.MRN From Membership As Z Where len(Z.[THRU-DT])=0))
ORDER BY MemNAME, [FROM-DT] DESC'
SELECT @.paramlist = '@.xMRN int,
@.xMemName nvarchar(40),
@.xDOB datetime,
@.xSSN nvarchar(9),
@.xSEX nvarchar(1)'
EXEC sp_executesql @.sql, @.paramlist,
@.MRN, @.MemNAME, @.DOB, @.SSN, @.SEX
But there must be a way to do this in two phases because trying it all in
one pull is just not efficient.
"Roji. P. Thomas" wrote:

> Have a look at
> http://www.sommarskog.se/dyn-search.html
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jonefer" <jonefer@.discussions.microsoft.com> wrote in message
> news:0FFF3A3A-AB66-4A96-B63E-73E937F7A164@.microsoft.com...
>
>|||How about...
SELECT MRN, Name, Sex, DOB, SSN
FROM dbo.Membership
WHERE (MRN = @.MRN or @.MRN is null)
AND (Name like @.Name or @.Name is null)
AND (Sex = @.Sex or @.Sex is null)
etc.
TIP: If there are not user specific tables, always specify the owner of the
object so that it is not ambiguous and SQL Server does not have to check for
<userid>.Membership and if not found, check for dbo.Membership. This applie
s
for referencing tables, views, and stored procedures. Also, never prefix a
stored procedure name with sp_ or xp_ as that can cause unexpected side
effects.
Just my two cents,
Joe
"jonefer" wrote:

> I have a search app that I'm developing in ASP.NET
> It has 5 parameters
> MRN, Name, Sex, DOB, SSN
> All or any combination can be used.
> what is the best way to handle the non-supplied parameters?
> i.e. if the user selects only supplies 'Name' and 'MRN'
> I want the SQL for the stored procedure to read as if it were just 2
> parameters:
> (but have one stored procedure that has parameter holders for all 5)
> Select MRN, Name, Sex, DOB, SSN
> From Membership
> WHERE MRN = 123456 AND Name like 'Johns%'
> The way I have been handling this is to rewrite a different SQL statement
> for every possible situation, and pass that string to the adapter. But I
can
> see that I'm not taking advantage of the processing that SQL server could
do
> in a stored procedure, and for 1.4 Million records, my app is just timing
out
> all the time.
> This must be a common issue. Could someone show me the ideal way to handl
e
> this?
>|||Joe
If you use sp_XXXXXX SQL server looks into MAster database first even if you
mentioned the database.sp_XXX... So, we are looking unnecessarily in master
database . that is why most folks suggest do not use sp_XXXX .I never heard
of side effects... If you know any please share with us.
--
SQL SERVER DBA
"Joe from WI" wrote:
> How about...
> SELECT MRN, Name, Sex, DOB, SSN
> FROM dbo.Membership
> WHERE (MRN = @.MRN or @.MRN is null)
> AND (Name like @.Name or @.Name is null)
> AND (Sex = @.Sex or @.Sex is null)
> etc.
> TIP: If there are not user specific tables, always specify the owner of th
e
> object so that it is not ambiguous and SQL Server does not have to check f
or
> <userid>.Membership and if not found, check for dbo.Membership. This appl
ies
> for referencing tables, views, and stored procedures. Also, never prefix
a
> stored procedure name with sp_ or xp_ as that can cause unexpected side
> effects.
> Just my two cents,
> Joe
> "jonefer" wrote:
>