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
@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
[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],
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 + '%'
Person.HLCI LIKE @HLCI + '%' AND
Person.SSN LIKE @SSN + '%'
)
GO
GO
dynamic sql help
JesperChristensen
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
@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/Frank Leonardi