temp tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

temp tables

Author

Topic

csadlier
Starting Member

1 Post

Posted - 2006-07-13 : 16:16:41

Why does this procedure errors with an Error 137. You must declare variable @how?
CREATE PROCEDURE GetInvoice @Invoice varchar(30) AS
DECLARE @TSQL varchar(8000)
DECLARE @how TABLE
(
Z_ID varchar(30),
SALES_REPORT_CUSTOMER varchar(50) ,
SHIP_DATE smalldatetime,
INV_AMT decimal(12, 2),
ORDER_TYPE varchar(10) ,
SALES_MARKET_SEGMENT varchar(75) ,
ORDER_DATE smalldatetime ,
PONO varchar(25) ,
POST_DATE smalldatetime ,
SUM_MISC_AMT float ,
WPS varchar(50),
TERMS_DESC varchar(75),
BOL varchar(50),
BILL_TO nvarchar(12) ,
BILL_NAME varchar(50) ,
BILL_ADDR1 varchar(50) ,
BILL_ADDR2 varchar(50) ,
BILL_CITY varchar(50) ,
BILL_STATE varchar(15),
BILL_ZIP varchar(10) ,
SHIP_TO varchar(15) ,
REAL_SHIPTO_NAME varchar(50) ,
REAL_SHIPTO_ADDRESS1 varchar(50) ,
REAL_SHIPTO_ADDRESS2 varchar(50) ,
REAL_SHIPTO_CITY varchar(50) ,
REAL_SHIPTO_STATE varchar(15) ,
REAL_SHIPTO_ZIP varchar(10) ,
SVI_DESC varchar(75) ,
INV_DATE smalldatetime ,
COMMENTS varchar(254) ,
SALESMAN varchar(5) ,
TAXAMT decimal(10, 2),
FGT_AMT decimal(10, 2) ,
INS decimal(10, 2),
Primary Key ( Z_ID)
)

SELECT @TSQL = 'SELECT * INTO @how h FROM OPENQUERY(UVADVANTAGE, ''SELECT * FROM OW WHERE @ID = ''''' + @Invoice + ''''''')'
EXEC(@TSQL)
IF @how is null
SELECT @TSQL = 'INSERT INTO @how h SELECT * FROM OPENQUERY(UVADVANTAGE, ''SELECT * FROM ROW WHERE @ID = ''''' + @Invoice + ''''''')'
EXEC(@TSQL)
IF @how is null
SELECT @TSQL =' INSERT INTO @how h SELECT * FROM OPENQUERY(UVADVANTAGE, ''SELECT * FROM HOW WHERE @ID = ''''' + @Invoice + ''''''')'
EXEC(@TSQL)

SELECT * FROM @how

GO

Carl Sadlier

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-07-13 : 16:23:10

@how is a table variable, and exists only within the scope of the procedure that creates it. Your dynamic SQL executes within an independent scope, and is thus unaware of the @how table.

To fix this, use a temporary table instead of a table variable. This will be visible within both execution scopes.

But you will have to fix this as well:

IF @how is null

I assume you mean to test if there are no rows in the table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources