dynamic sql help

I have a search procedure tied to my .net GUI. One value is required out of the 3 parameters I send it. The other 2 could be null, not null etc or any combination

The problem is the parameters are placed into the where clause. Is there a way to do an if statement of sorts in the where clause so that only the values that are not null end up in that part of the statement? I want to handle this in the sproc instead of reverting to a dynamically created where clause statement in my .net code.

Is this possible? Below is my sproc

CREATE PROCEDURE [dbo].[usp_SearchSpecialistCase]

@.LastName varchar(50)=NULL,
@.HLCI varchar(50)=NULL,
@.SSN char(9)=NULL
AS

SELECT
[PersonID],
[Case].[CaseID],
Person.[SSN],
Person.[HLCI],
UPPER(Person.LastName + ', '+ Person.FirstName + ' ' + ISNULL(Person.MiddleName, '')) as

FullName,
Person.[LastName],
Person.[MiddleName],
Person.[FirstName],
Person.[BirthDate],
Person.[HeadofHousehold],
SecurityUsers.SecurityOfficeID
FROM
[Person] INNER JOIN [Case] ON Person.CaseID = [Case].CaseID
LEFT JOIN CaseAssignedToHistory ON [Case].CaseID = CaseAssignedToHistory.CaseID
LEFT JOIN SecurityUsers ON CaseAssignedToHistory.UserID = SecurityUsers.UserID
LEFT JOIN SecurityOffice ON SecurityUsers.SecurityOfficeID = SecurityOffice.SecurityOfficeID
WHERE (

Person.LastName LIKE @.LastName + '%' AND
Person.HLCI LIKE @.HLCI + '%' AND
Person.SSN LIKE @.SSN + '%'

)
GO

mmmmm what about this?

select * from authors
where au_fname like 's%'

select * from authors
where au_fname like 's%'
and au_id like '%'

that is the same right?

then you could do this

CREATE PROCEDURE [dbo].[usp_SearchSpecialistCase]

@.LastName varchar(50)=NULL,
@.HLCI varchar(50)=NULL,
@.SSN char(9)=NULL
AS
SELECT
[PersonID],
[Case].[CaseID],
Person.[SSN],
Person.[HLCI],
UPPER(Person.LastName + ', '+ Person.FirstName + ' ' + ISNULL(Person.MiddleName, '')) as
FullName,
Person.[LastName],
Person.[MiddleName],
Person.[FirstName],
Person.[BirthDate],
Person.[HeadofHousehold],
SecurityUsers.SecurityOfficeID
FROM
[Person] INNER JOIN [Case] ON Person.CaseID = [Case].CaseID
LEFT JOIN CaseAssignedToHistory ON [Case].CaseID = CaseAssignedToHistory.CaseID
LEFT JOIN SecurityUsers ON CaseAssignedToHistory.UserID = SecurityUsers.UserID
LEFT JOIN SecurityOffice ON SecurityUsers.SecurityOfficeID = SecurityOffice.SecurityOfficeID
WHERE (
Person.LastName LIKE coalesce(@.LastName,'') + '%' AND
Person.HLCI LIKE coalesce(@.HLCI,'') + '%' AND
Person.SSN LIKE coalesce(@.SSN,'') + '%'
)
GO

Just check the execution plan for table scans etc, for the 2 queries above the execution plan is the same

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Please check out the link below for various suggestions/examples:

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