Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Stored Procedure help

    I have three tables.

    CompAcct (CompId, CompName, CompWebsite) - CompWebsite is unique
    Industry (IndustryId, IndustryName) - IndustryName is unique
    CompIndustry(CompId, IndustryId) - Composite key referencing CompAcct and Industry.

    I have a webform that asks a user to enter the following information for their company

    Enter Company Info
    Name: <Text Field>
    Website: <Text Field>
    Industry: <Dropdown List> - Populated from Industry table

    Trying to create this stored procedure to fill in the correct data for these tables. Here is my code. It does not seem that these two lines are valid. Any easy way to accomplish this?

    Invalid lines
    @CompId = SELECT CompAcct.Comp_CompID WHERE CompAcct.Comp_CompWebsite = @CompWebsite
    @IndustryId = SELECT Industry.Ind_IndustryId WHERE Industry.Ind_IndustryId = @IndustryName

    Complete code
    CREATE PROCEDURE dbo.createCompAcct
    -- Add the parameters for the stored procedure here
    @CompId as INT,
    @CompWebsite as VARCHAR(30),
    @CompName as VARCHAR(25),
    @CompPostal as VARCHAR(10),
    @CompCountry as VARCHAR(20),
    @CompPhone as VARCHAR(15),
    @IndustryId as INT,
    @IndustryName as VARCHAR(25)
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO TechDb.dbo.CompAcct(Comp_CompWebsite, Comp_CompName, Comp_CompPostal, Comp_CompCountry, Comp_CompPhone)
    VALUES (@CompWebsite, @CompName, @CompPostal, @CompCountry, @CompPhone)

    @CompId = SELECT CompAcct.Comp_CompID WHERE CompAcct.Comp_CompWebsite = @CompWebsite
    @IndustryId = SELECT Industry.Ind_IndustryId WHERE Industry.Ind_IndustryId = @IndustryName

    INSERT INTO TechDb.dbo.CompIndustry (CI_CompId, CI_IndustryId)
    VALUES (@CompId, @IndustryId)

    END

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bcahill View Post
    It does not seem that these two lines are valid.
    it does seem that they are missing the FROM clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    In addition to the FROM clause, you also need "set":

    set @CompId = SELECT CompAcct.Comp_CompID WHERE CompAcct.Comp_CompWebsite = @CompWebsite
    set @IndustryId = SELECT Industry.Ind_IndustryId WHERE Industry.Ind_IndustryId = @IndustryName

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Not to mention single-quotes!
    Code:
    SET @var = 'some text'
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    or just move the SELECTs

    Code:
    SELECT @CompId =  CompAcct.Comp_CompID WHERE CompAcct.Comp_CompWebsite = @CompWebsite
    SELECT @IndustryId =  Industry.Ind_IndustryId WHERE Industry.Ind_IndustryId = @IndustryName
    But what if there are 2? Oh no!
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Thrasymachus View Post
    or just move the SELECTs
    but don't forget your FROM clauses!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oh yeah that too. I am a crappy programmer. there are worse things in life.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus View Post
    or just move the SELECTs

    Code:
    SELECT @CompId =  CompAcct.Comp_CompID WHERE CompAcct.Comp_CompWebsite = @CompWebsite
    SELECT @IndustryId =  Industry.Ind_IndustryId WHERE Industry.Ind_IndustryId = @IndustryName
    But what if there are 2? Oh no!
    Simple:

    Code:
    SELECT @CompId =  COALESCE(@CompId, 0) + CompAcct.Comp_CompID WHERE CompAcct.Comp_CompWebsite = @CompWebsite
    SELECT @IndustryId =  Industry.Ind_IndustryId WHERE Industry.Ind_IndustryId = @IndustryName
    No wai...
    Last edited by Teddy; 05-06-10 at 15:56.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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