I have a table variable in SQL Server 2008
DECLARE @specsAndModel TABLE
(
specName VARCHAR(50)
,specVal VARCHAR(50)
)
INSERT INTO @specsAndModel
VALUES('[modelNumber]', 'F00-B4R')
Then, I later build a string called @query
, which I ultimately try to pass into EXECUTE
, as in the following example:
DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT specName, specVal FROM @specsAndModel'
EXECUTE(@query)
However, SQL Server gives me the error message: Must declare the table variable "@specsAndModel".
After searching around, I think this might be related to the execution context, but I haven't been able to resolve the problem.
Is it even possible for me to use a table variable in a call to the execute function?
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
The table you are creating is a table variable which is not available outside of its initial scope. There are a few ways to fix this:
Create a Global Temp Table (Disclaimer: this can cause problems if more that one user attempts to run this at the same time.):
create table ##specsAndModel
(
specName VARCHAR(50)
,specVal VARCHAR(50)
)
INSERT INTO ##specsAndModel
VALUES('[modelNumber]', 'F00-B4R')
DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT specName, specVal FROM ##specsAndModel'
EXECUTE(@query)
Create a Local Temp Table instead of global:
create table #specsAndModel
(
specName VARCHAR(50)
,specVal VARCHAR(50)
)
INSERT INTO #specsAndModel
VALUES('[modelNumber]', 'F00-B4R')
DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT specName, specVal FROM #specsAndModel'
EXECUTE(@query)
Execute the create table inside of your dynamic SQL (ugly):
DECLARE @query NVARCHAR(MAX);
SET @query = 'DECLARE @specsAndModel TABLE
(
specName VARCHAR(50)
,specVal VARCHAR(50)
)
INSERT INTO @specsAndModel
VALUES(''[modelNumber]'', ''F00-B4R'')
SELECT specName, specVal FROM @specsAndModel'
exec(@query)
Instead of using a temp table, create an actual table and then drop it when done (Disclaimer: this can cause problems if more that one user attempts to run this at the same time.):
create TABLE specsAndModel
(
specName VARCHAR(50)
,specVal VARCHAR(50)
)
INSERT INTO specsAndModel
VALUES('[modelNumber]', 'F00-B4R')
DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT specName, specVal FROM specsAndModel'
EXECUTE(@query)
drop table specsAndModel
Here is an link to a discussion about temp tables and table variables:
Should I use a #temp table or a @table variable?
Edit: You can pass in a table variable using sp_executesql
:
create type specsAndModel as table (
specName VARCHAR(50)
,specVal VARCHAR(50)
)
go
declare @t specsAndModel
insert @t VALUES('[modelNumber]', 'F00-B4R')
exec sp_executesql N'select specName, specVal from @var', N'@var specsAndModel readonly', @t
Using either the global ##temp tables and a permanent table run risks in that if more than one users attempts to run the process, there could be conflicts.
You are safer using either a local #temp table or passing the table variable using sp_executesql
.
You also have to create your table variable inside the in the string.
DECLARE @query NVARCHAR(MAX);
SET @query = 'DECLARE @specsAndModel TABLE ( specName VARCHAR(50) ,specVal VARCHAR(50))'
SET @Query = @Query + ' INSERT INTO @specsAndModel VALUES(''modelNumber'',''abcd''); SELECT specName, specVal FROM @specsAndModel'
EXEC (@query)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With