Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-27 : 12:11:26
|
Tony writes "Greetings, I have recently come across some stored procs that are creating over 600000 logical reads from a database. I wonder if you can tell me a better way of creating the stored proc listed below?
CREATE PROCEDURE pr_Contact_Search_client_No_address
@strUser VarChar(10) = " ",
@strReference VarChar(10) = " ",
@strSurname VarChar(20) = " ",
@strHouseNumber VarChar(4) = " ",
@strForename VarChar(20) = " ",
@strRoad VarChar(20) = " ",
@strCompany VarChar(20) = " ",
@strPostcode VarChar(10) = " ",
@strTown VarChar(30) = " ",
@strType VarChar(25) = " "
AS
DECLARE @Sql VarChar(5000)
SELECT @Sql = ' SELECT tParty.Party_ID,
tParty.PartyContactType_ID,
tLkpPartyContactType.PartyContactTypeDesc,
tPerson.Person_ID, tPerson.Forename, tPerson.Surname, '
SELECT @Sql = @Sql + 'tPerson.Middlename, tPerson.Title,
tAddress.PAFAddress_Id,'
SELECT @Sql = @Sql + 'CP_tOrganization.OrganizationName
AS CompanyPerson_CompanyName, '
SELECT @Sql = @Sql + ' tXEmployment.Person_ID as
EmpPer, ttelephoneFax.telFaxNo '
SELECT @Sql = @Sql + 'FROM tParty '
SELECT @Sql = @Sql + ' LEFT OUTER JOIN tLkpPartyContactType ON
tLkpPartyContactType.PartyContactType_ID =
tParty.PartyContactType_ID '
SELECT @Sql = @Sql + ' inner JOIN tclient on tclient.Client_ID
= tParty.Party_ID '
SELECT @Sql = @Sql + 'inner JOIN txemployment ON
txemployment.org_ID = tclient.Broker_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tOrganization ON
tOrganization.org_ID = tclient.Client_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tPerson ON
tPerson.person_ID = tclient.Client_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tCompany ON
tCompany.Company_ID = tclient.Client_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tAddress ON
taddress.Party_ID = tclient.Client_ID '/*attempted inner*/
SELECT @Sql = @Sql + ' inner join ttelephoneFax on
ttelephoneFax.party_id = tparty.party_id '
SELECT @Sql = @Sql + ' left outer join tlkpTeltype on
tlkpTeltype.teltype_id = ttelephoneFax.teltype_id '
SELECT @Sql = @Sql + ' left outer join ttelextension on
ttelextension.telFax_ID = ttelephoneFax.telFax_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tXPersOrgContact ON
tXPersOrgContact.Person_ID = TPerson.Person_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tOrganization AS
CP_tOrganization ON CP_tOrganization.org_ID =
tXPersOrgContact.Org_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tCompany AS CP_tCompany ON
CP_tCompany.Company_ID = CP_tOrganization.org_ID '
SELECT @Sql = @Sql + 'LEFT OUTER JOIN tAddress AS CP_tAddress
ON CP_tAddress.Party_ID = CP_tCompany.Company_ID '
SELECT @Sql = @Sql + 'WHERE tParty.DeletedFlag <> 1 '
/* Extra Criteria based upon the parameters passed into the stored procedure */
IF DATALENGTH(LTRIM(@strReference)) <> 0
BEGIN
SELECT @Sql = @Sql + 'AND tParty.Party_ID = ''' + @strReference + ''''
END
IF DATALENGTH(LTRIM(@strSurname)) <> 0
BEGIN
SELECT @Sql = @Sql + 'AND tPerson.Surname LIKE ''' + @strSurname + '% '''
END
IF DATALENGTH(LTRIM(@strHousenumber)) <> 0
BEGIN
SELECT @Sql = @Sql + 'AND tPAFMainAddressFile.[Building Number ] LIKE ''' + @strHousenumber + '%'''
END
IF DATALENGTH(LTRIM(@strForename)) <> 0
BEGIN
SELECT @Sql = @Sql + 'AND tPerson.Forename LIKE ''' + @strForename + '%'''
END
IF DATALENGTH(LTRIM(@strRoad)) <> 0
BEGIN
SELECT @Sql = @Sql + 'AND (tPAFThfare.ThoroughfareName + ' + ''' ''' + ' + tPAFThfareDesc.thoroughfareDescriptor) LIKE ''' + @strRoad + '%'''
END
IF DATALENGTH(LTRIM(@strCompany)) <> 0
BEGIN
SELEC |
|
|
|
|
|
|
|
Dynamic SQL? (possibly over 4000 chars sorry)