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

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|||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.|||dooooooh

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