Passing part of a where clause into a stored proc
-
Hi,
I have the following problem and wonder if someone can shed some light on it (Sql Server 2000).
My application allows the user to pass in the first letter of a customer surname, All customers or part of a surname and the appropriate results are then returned. Now they would like to optionally choose the column (at the moment if searching for a partial match only the surname can be used)
Any suggestions on how to do this. I suspect I will need to use some sort of dynamic code, but am not sure what.
SP as it currently stands: -
CREATE PROCEDURE [dbo].[prc0001CustomerMasterList]
(@strFirstLetter char(10),
@strFindName varchar(100) )
AS
If @strFindName = '' -- NOT SEARCHING FOR A NAME OR PART OF A NAME--
Begin
If @strFirstLetter = 'All' -- ALL CUSTOMERS REGARDLESS OF SPECIFIED LETTER --
Begin
Select A.intClientID,
A.intCaseNo,
A.intRecordedBy,
B.strSurname + ': ' + B.strForename as strRecordedBy,
A.intStatusID,
IsNull(C.strDescription, 'Uknown Status') as strStatus,
A.strTitle,
A.strSurname,
A.strFirstname,
A.strSecondName,
A.dtDOB,
A.strAddressLine1,
A.strHomeTel,
SubString(A.strRemarks, 1, 1) as strRemarks,
33554432 as color,
0 as found,
0 as selected
From tblCustomer A Left Outer Join
tblStaff B On A.intRecordedBy = B.intStaffID Left Outer Join
tblCustomerStatus C on A.intStatusID = C.intStatusID
Order By A.strSurname,
A.strFirstname,
A.strSecondName,
A.dtDOB
End
Else -- ALL CUSTOMERS STARTING WITH SPECIFIED LETTER --
Begin
Select A.intClientID,
A.intCaseNo,
A.intRecordedBy,
B.strSurname + ': ' + B.strForename as strRecordedBy,
A.intStatusID,
IsNull(C.strDescription, 'Uknown Status') as strStatus,
A.strTitle,
A.strSurname,
A.strFirstname,
A.strSecondName,
A.dtDOB,
A.strAddressLine1,
A.strHomeTel,
SubString(A.strRemarks, 1, 1) as strRemarks,
33554432 as color,
0 as found,
0 as selected
From tblCustomer A Left Outer Join
tblStaff B On A.intRecordedBy = B.intStaffID Left Outer Join
tblCustomerStatus C on A.intStatusID = C.intStatusID
Where SubString(A.strSurname,1,1) = @strFirstLetter
Order By A.strSurname,
A.strFirstname,
A.strSecondName,
A.dtDOB
End
End
Else -- SEARCHING FOR A NAME OR PART OF A NAME--
Begin
Select A.intClientID,
A.intCaseNo,
A.intRecordedBy,
B.strSurname + ': ' + B.strForename as strRecordedBy,
A.intStatusID,
IsNull(C.strDescription, 'Uknown Status') as strStatus,
A.strTitle,
A.strSurname,
A.strFirstname,
A.strSecondName,
A.dtDOB,
A.strAddressLine1,
A.strHomeTel,
SubString(A.strRemarks, 1, 1) as strRemarks,
33554432 as color,
0 as found,
0 as selected
From tblCustomer A Left Outer Join
tblStaff B On A.intRecordedBy = B.intStaffID Left Outer Join
tblCustomerStatus C on A.intStatusID = C.intStatusID
Where A.strSurname Like @strFindName
Order By A.strSurname,
A.strFirstname,
A.strSecondName,
A.dtDOB
End
GO
Any help most appreciated.
Thanks
CCB
-
Instead of
Where SubString(A.strSurname,1,1) = @strFirstLetter
I think I would write something like
Where
(SubString(A.strSurname,1,1) = @strFirstLetter and @SearchCol = 'SurName')
or
(SubString(A.strFirstname,1,1) = @strFirstLetter and @SearchCol = 'FirstName')
A similar change can be applied in the last section (where you use search like).
I think you should also consider rewriting your sp such that you don't have the same select 3 times with different where clauses. The same method - including variables in your where clause - can be applied. Let me know if you need help.
-
Hi,
That works a treat. I have changed the rest so that there is only one select statement.
Thanks for your help.
CCB
-
instead of
Where
(SubString(A.strSurname,1,1) = @strFirstLetter and @SearchCol = 'SurName')
or
(SubString(A.strFirstname,1,1) = @strFirstLetter and @SearchCol = 'FirstName')
I would first replace append a '%' to the @strFirstLetter then write:
Where
(A.strSurname like @strFirstLetter and @SearchCol='SurName')
or
(A.strFirstname like @strFirstLetter and @SearchCol='FirstName')
Reason being the SubString function or any function for that matter will suppresss any indexes you put on that column, causing a full table scan or index scan.
-
better yet, the where order should be changed to:
Where
(@SearchCol='SurName' and A.strSurname like @strFirstLetter)
or
(@SearchCol='FirstName' and A.strFirstname like @strFirstLetter )
This way, the db can make better use of short circuiting, so that the second condition is not evaluated if the first condition is false.
-
Sounds reasonable - still I didn't know
Thanks for the tips -
This way, the db can make better use of short circuiting, so that the second condition is not evaluated if the first condition is TRUE.
-
I think he means
such that "A.strSurname like @strFirstLetter" will not be evaluated if "@SearchCol='SurName'" is false
-
oops... but now if someone understands it like me he'll know about it both ways
. -
Yeah, it wasn't because what you said didn't make sense
See you all in 3 weeks (vacation
)Jesper
-
Hi all,
I meant exactly what i wrote
Where
(@SearchCol='SurName' and A.strSurname like @strFirstLetter)
or
(@SearchCol='FirstName' and A.strFirstname like @strFirstLetter )
This way, the db can make better use of short circuiting, so that the second condition is not evaluated if the first condition is false.
False AND ? = FALSE
True AND True/False = TRUE/FALSE depending on the second condition
So as soon as the first condition is FALSE then the second condition will not be evaluated.
If it's a OR, then only if the first condition is TRUE ; the second condition is not evaluated
TRUE AND ? = TRUE
FALSE AND ? = TRUE/FALSE depending on second condition
-
Ya I got what you really meant 2 minutes too late
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply