CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Darkrum
Darkrum

Reputation: 1373

Must declare the table variable SQL server 2008

I am writing to make it easier for other techs to fix a database issue.

The goal of this query is so the techs only have to copy paste and provided 3 values but I'm running into a error

Must declare the table variable "@fromArchives".

DECLARE @maxIdentityValue   INT
DECLARE @selectId   varchar(60)
DECLARE @fromArchives   varchar(60)
DECLARE @tableDBCC  varchar(60)

SET @selectId = someColumn --This is a column in the @fromArchives
SET @fromArchives = OrderArchives --This is a table
SET @tableDBCC = Orders --This is the table to fix


SET @maxIdentityValue =
(SELECT MAX(@selectId)
FROM @fromArchives) + 1


DBCC CHECKIDENT 
(@tableDBCC, RESEED, @maxIdentityValue)
GO

any ideas?

Upvotes: 0

Views: 1472

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8487

All tablename should be static in sql query.

If you want to use dynamic table or column names, you should generate the full SQL dynamically, and use sp_executesql or EXEC(sql) to execute it.

DECLARE @fromArchives varchar(60)
SET @fromArchives = OrderArchives --This is a table
EXEC('SELECT * FROM ' + @fromArchives)

Refer This Dynamic SQL Article

Upvotes: 3

Related Questions