Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    25

    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
    @AccountID char(10),
    @FromName nvarchar(50),
    @ToName nvarchar(50)
    AS

    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)
    GO

  2. #2
    Join Date
    Feb 2004
    Posts
    134
    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....

    Mike B

  3. #3
    Join Date
    Jun 2004
    Posts
    25
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And what do you get if you run this instead:

    select *
    from EndoscopySqlUser.Territories
    where Territories.TerritoryName=@ToName
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2004
    Posts
    25
    I enter this into Query Analyzer, eliminating the variable @ToName:

    select *
    from EndoscopySqlUser.Territories
    where Territories.TerritoryName= 'Albany'

    And it returns the proper record, with the information regarding the Albany territory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •