Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    11

    Unanswered: Creating a table inside a stored procedure

    I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

    Error 208: Invalid object name '##OPTIONSEX'

    I am using the following SQL script:

    CREATE PROCEDURE [dbo].[Test2] AS

    CREATE TABLE ##OPTIONSEX
    (
    OPTION_PLAN VARCHAR(50),
    TOT_OPTIONS_EXCHANGED FLOAT NULL
    )

    GO

    INSERT ##OPTIONSEX

    SELECT
    B.COMPONENT,
    TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
    FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
    WHERE B.COMPONENTID = A.COMPONENTID
    GROUP BY B.COMPONENT

    GO

    Any help getting this to run correctly would be appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is that like the type of sex you want....is this mail order?

    Anyway, you need to take out the GO...that's a scope terminator

    And are you sure you want a GLOBAL Temp table instead of a local one?

    Try this

    Code:
    CREATE PROCEDURE [dbo].[Test2] 
    AS
    BEGIN
    	CREATE TABLE ##OPTIONSEX (
    		  OPTION_PLAN VARCHAR(50)
    		, TOT_OPTIONS_EXCHANGED FLOAT NULL
    )
    
       INSERT   ##OPTIONSEX
       SELECT  B.COMPONENT
    	 , TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
          FROM TBLEXERCISEOPTIONS A
    INNER JOIN TBLCOMPONENT B
    	ON B.COMPONENTID = A.COMPONENTID
      GROUP BY B.COMPONENT
    
    DROP TABLE ##OPTIONSEX
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Posts
    11

    Creating a table inside a stored procedure

    Thanks for the reply. Worked fine only after I removed the BEGIN line. For some reason using BEGIN returned a syntax error.

    Thanks again Brett.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [homer]

    dooooooh

    [/homer]

    I forgot the END

    BEGIN
    ......some code
    END

    And you'll need those constructs if you do an control of flow logic

    IF some condition
    BEGIN
    ............some code line 1
    ............some code line 2
    END

    WHILE some Cond
    BEGIN
    ............some code line 1
    ............some code line 2
    END


    Good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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