Unanswered: What's wrong with this Stored Procedure?
I am having trouble getting this procedure to properly enter the data into a table. It works when I do not add the @ToName info and only ask to insert the AccountID and FromName data. When i add the @ToNamevariable, no data is inserted. I think it might have to do with the select statement. I have two TerritoryID, RegionID, DivisionID, and EmpID because I want this information to be entered for both the FromName and the ToName selected. Does anyone know what my problem is? Id appreciate any help.
CREATE PROCEDURE InsertAllThree
insert into accounttransferstestmike
(AccountID, FromTerritoryID, FromRegionID, FromDivisionID, FromEmpID, ToTerritoryID, ToRegionID, ToDivisionID, ToEmpID)
select Accounts.AccountID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID, Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, 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 (Accounts.AccountID = @AccountID) and (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromN ame) AND (EndoscopySqlUser.Territories.TerritoryName=@ToNam e)
I don't see anything wrong with it, but I didn't really check all that hard.
One way I found creating stored procedures (wrong ? I don't know), especially with multiple joins, is to first create a view with the joined tables showing the fields I need. Then create the stored proc to extract from these views.
Makes it alot easier. If you don't want to do it this way, then create the view, copy the SQL statement into the stored proc....
I did that. I took the select directly from the view. My problem is having it recognize a TerritoryID, RegionID, DivisionID, and EmpID for the @ToName that the user selects. It works fine selecting and inserting these for the @FromName value selected, but I can't seem to grab different ID's for the ToName and the FromName, respectively