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?

Have a look at

http://www.sommarskog.se/dyn-search.html

Show quoteHide quote

Thank you.  That was excellent.

Show quoteHide quote

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.

Show quoteHide quote

Show quote Hide quote 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 ...

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 applies
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

Show quoteHide quote

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.
Show quoteHide quote

Use of NULL and DEFAULT
Record locking within a stored procedure
How to decide whether trigger is a good idea?
compare '002' and '00²'
Evaluation of conditions