Stored procedure help - 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 2008 Forums

Transact-SQL (2008)

Stored procedure help

Author

Topic

bplogan
Starting Member

6 Posts

Posted - 2012-02-20 : 13:48:03

Hello, I am trying to write a stored procedure that gets account data based off of account relationships. My tables are as below:

The TEMP_Account table holds every account the exists.
The TEMP_AccountType table hold the different account types (1-5)
The Temp_AccountParent table hold the relationships or 'chains' of the accounts.

To explain the TEMP_AccountParent table:
I get account 'chains' from the customer which consist of 5 account ID's in order of account type. So they could give me 11,22,33,44,55 as a chain. I save this in the TEMP_AccountParent table like so:

The keyID is the ID of the previous row in the chain. The KeyID of -1 represents the beginning of the chain.
(this is the only way I could think of storing these 'chains' of account relationships).

I have 5 dropdowns, one for each account type:

The first dropdown fills with the accounts of type 1. When I select an account, the TEMP_AccountParent table is queried for all rows having ParentID of the one I selected and also having account type of 2. The second dropdown is then filled with these results. The stored procedure up to this point is very simple. Its when I select in the 3rd dropdown and on, that I can't get a single stored procedure to get the results I need.

When an account is selected in the 3rd dropdown, I have to get from the parent table, all rows that have the parentID of the 2nd dropdown, but then I have to filter those results with having the parentID of the 1st dropdown.

I'm hoping that makes some kind of sense to somebody. It's hard to explain with words...Anyway, any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 14:27:19

so effectively once you select an account you want all its parents upto different levels?

something like


With Account_hierarchy
AS
(
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
 UNION ALL
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
INNER JOIN TEMP_AccountParent ap
ON ap.ParentID = a.ID
INNER JOIN Account_hierarchy ah
ON ah.ID = ap.ChildID
WHERE ah.KeyID <>-1
)
SELECT *
FROM Account_Hierarchy
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplogan
Starting Member

6 Posts

Posted - 2012-02-20 : 15:46:04

quote:
Originally posted by visakh16

so effectively once you select an account you want all its parents upto different levels?

something like

With Account_hierarchy
AS
(
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
UNION ALL
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
INNER JOIN TEMP_AccountParent ap
ON ap.ParentID = a.ID
INNER JOIN Account_hierarchy ah
ON ah.ID = ap.ChildID
WHERE ah.KeyID <>-1
)
SELECT *
FROM Account_Hierarchy
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi, thanks for the reply, I am getting an error saying invalid column 'keyID' @ WHERE ah.KeyID <>-1 in your code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 15:48:40

[code]
;With Account_hierarchy
AS
(
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName,ap.KeyID
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
INNER JOIN TEMP_AccountParent ap
ON ap.ParentID = a.ID

UNION ALL
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
INNER JOIN TEMP_AccountParent ap
ON ap.ParentID = a.ID
INNER JOIN Account_hierarchy ah
ON ah.ID = ap.ChildID
WHERE ah.KeyID <>-1
)
SELECT *
FROM Account_Hierarchy
OPTION (MAXRECURSION 0)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplogan
Starting Member

6 Posts

Posted - 2012-02-20 : 15:53:28

Thanks, now I am getting the following error:

"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 15:57:56

[code]
;With Account_hierarchy
AS
(
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName,ap.KeyID
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
INNER JOIN TEMP_AccountParent ap
ON ap.ParentID = a.ID

UNION ALL
SELECT a.Number,a.Description,a.ID,at.Name AS TypeName,ap.KeyID
FROM TEMP_Account a
INNER JOIN TEMP_AccountType at
ON at.ID = a.TypeID
INNER JOIN TEMP_AccountParent ap
ON ap.ParentID = a.ID
INNER JOIN Account_hierarchy ah
ON ah.ID = ap.ChildID
WHERE ah.KeyID <>-1
)
SELECT *
FROM Account_Hierarchy
OPTION (MAXRECURSION 0)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplogan
Starting Member

6 Posts

Posted - 2012-02-20 : 16:02:57

Ok, so that gives me a massive result set that takes about 5 min to load. How would I use is to:

Populate the 3rd dropdown after selecting something in the 2nd dropdown. Say I select ID of 397 in the first dropdown, 456 in the second dropdown. I would be looking for all account of type 3, which have 397 and 456 in the 'chain'..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 16:09:38

filter on the typeid or typename fields based on your rule

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplogan
Starting Member

6 Posts

Posted - 2012-02-21 : 08:45:19

Hi, sorry I'm just not following. I've tried filtering every way I could think of and still can't get the correct results. What do you think the query should look like if I had selected the following:

Dropdown1 - account ID of 397
Dropdown2 - account ID of 456

I appreciate the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:19:40

post how you want result to come for above case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bplogan
Starting Member

6 Posts

Posted - 2012-02-21 : 15:46:10

Hi, I would want all the childID's that have 456 as a parent ID, and then need to filter those results for the first selection on 397.

I start by selecting 397, I get all childID's that have 397 as a parent. I fill dropdown 2 with those results. I then select 456 from dropdown 2, and I need to get the children of 456 but need to take in account the selection I have made in dropdown 1 of 397.

For every selection I make after the first one, I have to make sure the childID's I am returning for the next dropdown exist in the 'chain'.

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources