Efficient Stored Proc - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Efficient Stored Proc

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-14 : 04:13:11

I have the following stored procedure that I am not sure if it is written in the most "efficient" manner or if there is a better way. We have hardcoded fields that we are looking for with hard coded descriptions (otherwise this proc would probably be an sql statement within our app). But as fields are added, we do not want to have to recompile: thus the sp. This example only includes a small number of the fields we are getting from this sp. This sp could grow very lengthy with the addition of new fields (50-100). It already seems so HUGE and all because of my bit parameter. I just wanted to see if there was a "better" way. The only difference between the two if sections is the WHERE Clause.

CREATE PROCEDURE spGetQuickSelectionValues
(@ll_EntitySubTypeID int,
@lb_DisplayAll bit) AS

--Viewing All Entities
If @lb_DisplayAll =1
BEGIN
SELECT
Entity.TitleDesc AS "Title",
Entity.FirstName as "FirstName",
Entity.MiddleInitial as "MiddleInitial",
Entity.LastName as "LastName",
Entity.SuffixDesc as "Suffix",
EntityMember.RelationshipENUM as "Relationship",
EntityMember.TypeID as "MemberType"
FROM Entity
INNER JOIN EntityMember
ON Entity.ID = EntityMember.EntityID
WHERE EntityMember.EntitySubTypeID = @ll_EntitySubTypeID
END
Else

--Viewing Only Main Entities
BEGIN
SELECT
Entity.TitleDesc AS "Title",
Entity.FirstName as "FirstName",
Entity.MiddleInitial as "MiddleInitial",
Entity.LastName as "LastName",
Entity.SuffixDesc as "Suffix",
EntityMember.RelationshipENUM as "Relationship",
EntityMember.TypeID as "MemberType"
FROM Entity
INNER JOIN EntityMember
ON Entity.ID = EntityMember.EntityID
WHERE EntityMember.EntitySubTypeID = @ll_EntitySubTypeID
And EntityMember.ParentEntityID = 0
END
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-14 : 08:25:49

Yes, you can simplify this a bit:

CREATE PROCEDURE spGetQuickSelectionValues
(@ll_EntitySubTypeID int,
@lb_DisplayAll bit) AS

SELECT
Entity.TitleDesc AS "Title",
Entity.FirstName as "FirstName",
Entity.MiddleInitial as "MiddleInitial",
Entity.LastName as "LastName",
Entity.SuffixDesc as "Suffix",
EntityMember.RelationshipENUM as "Relationship",
EntityMember.TypeID as "MemberType"
FROM Entity
INNER JOIN EntityMember
ON Entity.ID = EntityMember.EntityID
WHERE EntityMember.EntitySubTypeID = @ll_EntitySubTypeID
And EntityMember.ParentEntityID = CASE @lb_DisplayAll WHEN 0 THEN 0 ELSE EntityMember.ParentEntityID END

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-14 : 23:18:47

Works like a charm! Thanks!!

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-16 : 02:01:03

Just as I suspected, this stored procedure got more complicated the minute that I have everything working perfectly. I will post a shortened version of the sp (without all the fields). Basically what I need is this: a list of every entity record regardless of the remaining fields data. I have changed my joins to Full Joins for this reason. The problem came up when I added addresses into the mix. I will need one address (the HOME address) for each entity. I know what address is the HOME address by the AddressSubTypeID. If it is null, great. If it is not null great. The problem is that my sp is returning the entity record for every address. Although it is returning everyone now like I want, it is also returning some entities many times for as many addresses as they have instead of for just their Home address. Any ideas?

CREATE PROCEDURE spGetQuickSelectionValues
(@ll_EntitySubTypeID int,
@lb_DisplayAll bit,
@ll_Sort int) AS

SELECT
(E.LastName + ', ' + E.FirstName) as "LastNameFirstName",
E.Number as "Number",
EM.RelationshipENUM as "Relationship",
EA.Address1 as "HomeAddress1",
EA.Address2 as "HomeAddress2",
EA.CityDesc as "HomeCity",
EA.StateDesc as "HomeState",
EA.PostalCodeDesc as "HomePostalCode",
(EA.Address1 + ' ' + EA.CityDesc + ' ' + EA.StateDesc + ' ' + EA.PostalCodeDesc) as "ShortHomeAddress"

FROM Entity as E
FULL JOIN EntityMember as EM
ON E.ID = EM.EntityID
FULL JOIN EntityAddress as EA
ON E.ID = EA.EntityID
LEFT JOIN AddressSubType as AST
ON EA.AddressSubTypeID = AST.ID
AND AST.IsHomeAddress = 1
WHERE EM.EntitySubTypeID = @ll_EntitySubTypeID
AND EM.ParentEntityID = CASE @lb_DisplayAll
WHEN 0 THEN 0
ELSE EM.ParentEntityID END
ORDER BY Case @ll_Sort
WHEN 0 then E.LastName
WHEN 1 then E.Number + E.NumberSuffix END

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-16 : 10:12:39

quote:
Basically what I need is this: a list of every entity record regardless of the remaining fields data.

If you need every Entity record then all you need are left joins everywhere, not full joins. A full join will return matching and non matching data from both tables. Will this work?

CREATE PROCEDURE spGetQuickSelectionValues
(@ll_EntitySubTypeID int,
@lb_DisplayAll bit,
@ll_Sort int) AS

SELECT
(E.LastName + ', ' + E.FirstName) as "LastNameFirstName",
E.Number as "Number",
EM.RelationshipENUM as "Relationship",
EA.Address1 as "HomeAddress1",
EA.Address2 as "HomeAddress2",
EA.CityDesc as "HomeCity",
EA.StateDesc as "HomeState",
EA.PostalCodeDesc as "HomePostalCode",
(EA.Address1 + ' ' + EA.CityDesc + ' ' + EA.StateDesc + ' ' + EA.PostalCodeDesc) as "ShortHomeAddress"

FROM Entity as E
LEFT JOIN EntityMember as EM
ON E.ID = EM.EntityID
LEFT JOIN EntityAddress as EA
ON E.ID = EA.EntityID
LEFT JOIN AddressSubType as AST
ON EA.AddressSubTypeID = AST.ID
AND AST.IsHomeAddress = 1
WHERE EM.EntitySubTypeID = @ll_EntitySubTypeID
AND EM.ParentEntityID = CASE @lb_DisplayAll
WHEN 0 THEN 0
ELSE EM.ParentEntityID END
ORDER BY Case @ll_Sort
WHEN 0 then E.LastName
WHEN 1 then E.Number + E.NumberSuffix END

Owais

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-16 : 10:53:38

Changing the joins to Left gives me the same results, but I did anyway only because all my test data matches record for record where in a client site, it may not. Thanks for that. Unfortunately it does not solve my other problem with addresses. Imagine this data:

Entity table data
ID FirstName LastName
1 John Smith
2 Jim Jackson

EntityMember table data
ID Number
1 1234
2 5678

AddressSubType table data
ID Description IsHomeAddress
1 Home 1
2 Business 0
3 Personal email 0

EntityAddress table data
ID EntityID AddressSubTypeID AddressDesc
1 1 1 123 Ave C, Dallas TX
1 1 2 456 Main St Ste 3 Dallas
1 1 3 jsmith@abccompany.com

Now, in a perfect world, my sp would return:
John Smith 1234 123 Ave C, DAllas TX
Jim Jackson 5678 null

It is returning both records like I want, but instead it also returning John Smith three times for every address he has. HELP! I keep telling myself there has to be a way to do this and I am just not getting it.

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-19 : 11:51:56

quote:

HELP! I keep telling myself there has to be a way to do this and I am just not getting it.

Maybe I was wrong???? If I am off base here and what I am wanting to accomplish is not possible, could someone let me know so I will stop racking my brain (or whats left of it)? Thanks so much!

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-19 : 12:13:34

First,
Don't just pretty print table structures and contents, INCLUDE THE DML AND DDL so we don't have to ...

create table #entity
(
entityID int,
FirstName varchar(50),
LastName varchar(50)
)

insert #entity (entityID, firstname, lastname)
select 1, 'John', 'Smith' union
select 2, 'Jim', 'Jackson'

create table #entitymember
(
entityID int,
number int
)

insert #entitymember (entityID, number)
select 1, 1234 union
select 2, 5678

create table #addresssubtype
(
addresssubtypeID int,
description varchar(50),
isHomeAddress bit
)

insert #addresssubtype (addresssubtypeID, description, ishomeaddress)
select 1, 'Home', 1 union
select 2, 'Business', 0 union
select 3, 'Personal email', 0

create table #EntityAddress
(
EntityAddressID int,
entityID int,
addresssubtypeID int,
address varchar(50)
)

insert #EntityAddress (EntityAddressID, entityID, addresssubtypeID, address)
select 1, 1, 1, '123 Ave C, Dallas TX' union
select 1, 1, 2, '456 Main St Ste 3 Dallas' union
select 1, 1, 3, 'jsmith@abccompany.com'

/*
Now, in a perfect world, my sp would return:
John Smith 1234 123 Ave C, DAllas TX
Jim Jackson 5678 null
*/

select e.firstname, e.lastname, m.number, a.address
from #entity e
inner join #entitymember m on e.entityID = m.entityID
left join #entityaddress a on ( addresssubtypeID = 1 and a.entityID = e.entityID )

q: what's the difference between entity and entitymember??

Jonathan
{0}

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-19 : 12:37:20

Ok. I will lay my cards on the table. :I I dont know what "INCLUDE THE DML AND DDL" means, so I appologize for making my request more work for someone. That was not the intent. I am new to all of this. A friend referred to me to this website and I am addicted now!!

EntityMember and Entity contain different fields. EntityMember is a child table of Entity. Entity will contain the basic information such as name,gender, etc. EntityMember will contain information that may vary for each way that you are an member of the organization. I could belong to the "club" and also be a vendor, in which case I would be in EntityMember twice.

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-19 : 12:48:37

quote:

select e.firstname, e.lastname, m.number, a.address
from #entity e
inner join #entitymember m on e.entityID = m.entityID
left join #entityaddress a on ( addresssubtypeID = 1 and a.entityID = e.entityID )

This works perfectly except that I dont know what ID addressSubTypeID I need. In this example, the IsHomeAddress ID could be 1, but not always.

I mess it up as soon as I add the join to the AddressSubType table

JAdauto
Posting Yak Master

160 Posts

Posted - 2003-06-19 : 13:04:40

I could set a parameter on the stored procedure for @ll_AddressSubTypeID. Then I could get the home address before I run the sp. I would not have to join AddressSubType at all then. Would you suggest I go this route?

Cant thank you enough,
Jen

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-06-19 : 13:15:16

DML = Data Manipulation Language
DDL = Data Definition Langauge

which in plain terms are the CREATE + INSERT statements below....

when you want help....always think in terms of what a total stranger would appreciate as tools to have when analysing your problem....it makes it easier for us to jump in and lend a hand.

re determining the subaddress....is there a way via code that this can be determined for each client? is it the highest/lowest value subaddress...does it have particular charateristics....or is it a case of "Because I'm a human, I can just recognise a HOME address".

Remember the CPU doesn't have your eyes...it needs a bit more help.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-19 : 13:47:29

OH why didn't you say so?

select e.firstname, e.lastname, m.number, a.address
from #entity e
inner join #entitymember m on e.entityID = m.entityID
left join (
select a.entityID, a.addresssubtypeID, a.address
from #addresssubtype t
inner join #entityaddress a on (a.addresssubtypeID = t.addresssubtypeID and t.ishomeaddress = 1)) a
on a.entityID = e.entityID

Jonathan
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources