Need help fixing up this stored procedure

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

SQL Server Development (2000)

Need help fixing up this stored procedure

Author

Topic

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-16 : 15:32:41

Hello. here is a stored procedure I have made. Basically, I have two drop down lists on a form. One contains account numbers and the other contains territories to transfer that account to. One of the items in the Territories DDL is called Inside Sales, which is broken down into a number of different departments. So what I want to happen is when the user selects this particular item, Inside Sales, the procedure looks up the state the account selected is located in, and insert the proper IS number (i.e. is1, is2, is3, etc.) that is associated with that state. Here is how I have started off, but I am confused as to how i would go about writing this if statement IF 'Inside Sales' is selected.


CREATE PROCEDURE [UpdateToTerritory]

@AccountID Char(10),
@ToName nvarchar(50)

AS UPDATE accounttransferstestmike


Set ToTerritoryID = territories.TerritoryID, ToRegionID = Regions.RegionID, ToDivisionID = Divisions.DivisionID, ToEmpID = Employees.EmployeeID
FROM accounts2, Accounts, ISD, EndoscopySqlUser.Territories INNER JOIN 
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN 
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN 
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID 

WHERE @AccountID = Accounts.AccountID AND @ToName = territories.TerritoryName AND ToTerritoryID is NULL AND territories.CloseDate is NULL

if @ToName = 'Inside Sales' then
select Territories.TerritoryName like 'IS'
Where (Accounts.State = ISD.State)

GO

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-16 : 15:43:07

if (@toName = 'Inside Sales')
Begin

Select territories.TerritoryName From Accounts Inner Join ISD On Accounts.State = ISD.State Where Accounts.AccountId = @AccountId

End

i don't know where you are trying to go with that... but thats a start

Corey

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-17 : 11:00:31

Here is my stored procedure with the code you have given me. I check the syntax and it works fine, however when i change the name of the table ISD to ISDState (Since ISD doesn't exist and this is what it is called), I receive an error saying "The column prefix territories does not match with a table name or alias name used in the query." Whats going on here?

CREATE PROCEDURE [UpdateToTerritory]

@AccountID Char(10),
@ToName nvarchar(50)

AS UPDATE accounttransfers

Set ToTerritoryID = territories.TerritoryID, ToRegionID = Regions.RegionID, ToDivisionID = Divisions.DivisionID, ToEmpID = Employees.EmployeeID
FROM accounts, EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID

WHERE @AccountID = Accounts.AccountID AND @ToName = territories.TerritoryName AND ToTerritoryID is NULL AND territories.CloseDate is NULL
if (@toName = 'Inside Sales')
Begin

Select territories.TerritoryID From Accounts Inner Join ISDState On Accounts.State = ISDState.State Where Accounts.AccountId = @AccountId

End
GO

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-17 : 11:30:50

Here is an updated stored procedure. It runs properly, but if the user selects 'Inside Sales', the proper TerritoryID, RegionID, etc. are not inserted. The Territory ID inserted is the default 000000000 value for the record 'Inside Sales' in the territories table. I want to look up the exact Inside Sales Territory (IS 1, IS 2, etc.) that matches the state the account is located in.

CREATE PROCEDURE [UpdateToTerritory]

@AccountID Char(10),
@ToName nvarchar(50)

AS UPDATE accounttransfers

Set ToTerritoryID = territories.TerritoryID, ToRegionID = Regions.RegionID, ToDivisionID = Divisions.DivisionID, ToEmpID = Employees.EmployeeID
FROM accounts, EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID

WHERE @AccountID = Accounts.AccountID AND @ToName = territories.TerritoryName AND ToTerritoryID is NULL AND territories.CloseDate is NULL
if (@toName = 'Inside Sales')
Begin
update accounttransfers

Set ToTerritoryID = territories.TerritoryID, ToRegionID = Regions.RegionID, ToDivisionID = Divisions.DivisionID, ToEmpID = Employees.EmployeeID
FROM EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, Accounts Inner Join ISDState On Accounts.State = ISDState.StateAbb
Where Accounts.AccountId = @AccountId

End
GO

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-17 : 12:00:22

I have added this to the where clause at the bottom of the stored procedure:
Where Accounts.AccountId = @AccountId and ISDState.Territory = Territories.TerritoryName

But now, nothing is inserted into the table at all!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 12:37:44

quote:
Originally posted by cusoxty

I have added this to the where clause at the bottom of the stored procedure:
Where Accounts.AccountId = @AccountId and ISDState.Territory = Territories.TerritoryName

But now, nothing is inserted into the table at all!

How can you use ISDState when I don't see it in any of your from clauses??

Plus you might want to double check your inner joins as they can narrow your result set

Corey

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-17 : 13:00:02

Isn't it there?
FROM EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, Accounts Inner Join ISDState On Accounts.State = ISDState.StateAbb

I am just confused as to why no results are showing up when the data is inserted

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 13:06:31

oh sorry... must have missed that.

My guess would be that one of your inner joins is filtering the select.

inner join: show only records from table 1 that have a match in table 2
left join: show all records from table 1 and show matches from table 2

try changing your query to use left joins and see how many results you get

Plus you keep saying inserted... the procedure you show is an update. Update queries can not add records, they can only modify existing records.

Corey

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-17 : 13:10:18

Sorry. I do mean update. However, what do you mean by it could be filtering out the select? I dont think i have a select statement. About the joins, I am confident that there are matches in each of the tables, so the inner joins should be fine. Can you tell me in plain english, what this stored procedure is doing? This will help me realize where i am going wrong. I appreciate your help!

cusoxty
Constraint Violating Yak Guru

271 Posts

Posted - 2004-06-17 : 13:30:47

I took this out. Now i am back to inserting the 'Inside Sales' record info which shouldn't be entered.

CREATE PROCEDURE [UpdateToTerritory]

@AccountID Char(10),
@ToName nvarchar(50)

AS UPDATE accounttransfers

Set ToTerritoryID = territories.TerritoryID, ToRegionID = Regions.RegionID, ToDivisionID = Divisions.DivisionID, ToEmpID = Employees.EmployeeID
FROM accounts, EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID

WHERE @AccountID = Accounts.AccountID AND @ToName = territories.TerritoryName AND ToTerritoryID is NULL AND territories.CloseDate is NULL
if (@toName = 'Inside Sales')
Begin
update accounttransfers

Set ToTerritoryID = territories.TerritoryID, ToRegionID = Regions.RegionID, ToDivisionID = Divisions.DivisionID, ToEmpID = Employees.EmployeeID
FROM EndoscopySqlUser.Territories INNER JOIN
EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN
EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN
EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, Accounts Inner Join ISDState On Accounts.State = ISDState.StateAbb
Where Accounts.AccountId = @AccountId and ISDState.Territory = Territories.TerritoryName AND @toname = territories.TerritoryName AND ToTerritoryID is NULL AND territories.CloseDate is NULL

End
GO

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 14:28:48

Crap... i lost my post... all i have left is a query for you to run.

this should be the same as the if clause query:

Declare @accountId int,
@ToName nvarchar(100)

Set @accountID =
Set @toName =

Select
ToRegionId = (Select RegionId From EndoscopySqlUser.Regions Where Region = A.RegionName),
ToDivisionId = (Select Division From EndoscopySqlUser.Regions Where Region = A.RegionName),
ToEmpId = (Select EmployeeId From EndoscopySqlUser.Employees WHere TerritoryId = A.ToTerritoryId),
A.*
From
(
Select
ToTerritoryID = A.TerritoryID,
A.TerritoryName,
A.RegionName,
A.CloseDate
From EndoscopySqlUser.Territories as A
Inner Join ISDState as B
On B.Territory = A.TerritoryName
Inner join Accounts as C
On B.StateAbb = C.State
Where A.TerritoryName = @toname
and A.ToTerritoryID is null
and A.CloseDate is null
and C.AccountId = @AccountId
) as Z

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources