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

    Unanswered: Stored Procedure question

    Here is a stored procedure I made. This updates a record after I have already inserted the Account Number and the FromTerritory information associated with that account. However, i receive the data from two drop down lists, and one of the items in the ToTerritory drop down is called Inside Sales. When this item is selected from the drop down, I would like to check the state that the Account is currently in (from the account table) and match it with the Inside Sales number (we have IS 1, IS2, etc.) that is matched with that state.

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


    So.. if the user selects 'Inside Sales', I want to look up the State in Accounts, match it with the State in the Inside Sales table, and enter into the table the IS number that state is associated with. I hope this helps you help me!

  2. #2
    Join Date
    Jun 2004
    Posts
    25

    Something along these lines...

    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

    ????

Posting Permissions

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