table variables - 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)

table variables

Author

Topic

bob s
Starting Member

7 Posts

Posted - 2002-08-16 : 08:34:54

Hi,

When I run the following I get the error
"Must declare the variable '@TableVar'",
any ideas why??

DECLARE @TableVar table (col1 char (10) NOT NULL)
DECLARE @STRING varchar (100)
SET QUOTED_IDENTIFIER OFF
SELECT @STRING = "INSERT INTO " + @TableVar + " (col1) SELECT 'Sample'"
EXEC (@STRING)
SET QUOTED_IDENTIFIER ON

Cheers

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-16 : 08:40:31

Take a look at these:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

Look at the portions that discuss "scope". You declared the table variable outside of the dynamic SQL you're executing, therefore it's not visible to that snippet of code.

Also, a table variable cannot be concatenated (+) to a dynamic SQL string, it's not a string itself. You'd only have to have the following:

INSERT INTO @TableVar (col1) SELECT 'Sample'

You also used double quotes to delimit a string ("), this will cause an error in SQL Server. All string/character data must be delimited with single quotes (') (quoted_identifier settings don't always work right)

In fact, this should work fine:

DECLARE @TableVar table (col1 char (10) NOT NULL)
INSERT INTO @TableVar(col1) SELECT 'Sample'

Edited by - robvolk on 08/16/2002 08:41:19

bob s
Starting Member

7 Posts

Posted - 2002-08-16 : 09:01:21

Thanks for the response.

Sorry, I simplified the query somewhat...the reason for the dynamic sql is to insert values from a query string, maybe it should have read as follows...

DECLARE @TableVar table (col1 char (10) NOT NULL)
DECLARE @STRING varchar (100), @STRING1 varchar (100)
SELECT @STRING1 = 'SELECT col1 FROM sometable'
SET QUOTED_IDENTIFIER OFF
SELECT @STRING = "INSERT INTO " + @TableVar + " (col1) SELECT t1.Col1 FROM (" + @STRING1 + ") AS t1"
EXEC (@STRING)
SET QUOTED_IDENTIFIER ON

I've been using similar code which works fine (using temp tables), The quoted_identifier seems to be OK as well, could you expand on "(quoted_identifier settings don't always work right)".

Anyway, I guess it's back to Temp Tables :(

thanks

Edited by - bob s on 08/16/2002 09:02:35

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-16 : 09:10:27

The scoping for variables and temp table names is not the same.
Variables are lexically scoped, temp table names are dynamically scoped.

So this works:


CREATE TABLE #t (i int)
INSERT INTO #t VALUES (1)
EXEC ('SELECT * FROM #t')
DROP TABLE #t

But this doesn't:


DECLARE @t table (i int)
INSERT INTO @t VALUES (1)
EXEC ('SELECT * FROM @t')

And nor does this:


DECLARE @i int
SET @i = 1
EXEC ('SELECT @i')

Edited by - Arnold Fribble on 08/16/2002 09:11:27

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-16 : 09:23:33

The quoted identifier problem usually crops up with strings longer than 128 characters; SQL Server sees it as an object identifier and throws up because it's too long to be valid. I think this was more of a problem in SQL 6.5 than it might be with later versions, but in any event if you use single quotes to delimit strings you'll never have a problem.

If you use temp tables you can simplify your code a bit:

CREATE TABLE #tempTable (col1 char(10) NOT NULL)
DECLARE @STRING1 varchar (100)
SELECT @STRING1 = 'SELECT col1 FROM sometable'
INSERT INTO #tempTable EXECUTE (@STRING1)

There's no need to wrap the SELECT string inside another SELECT statement even if you use dynamic SQL.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-23 : 13:48:02

quote:

The scoping for variables and temp table names is not the same.
Variables are lexically scoped, temp table names are dynamically scoped.

bell labs, SML97, I'll get it.

quote:

STATIC vs DYNAMIC SCOPE

Static, or lexical scope rules
- Define scope in terms of the lexical structure of the program
Dynamic scope rrules
-Define scope based on the current state of program execution
-Scope can not always by examining the program because it is dependent on (dynamic) calling sequences

from http://www-edlab.cs.umass.edu/cs530/LectureSlides/lecture-10.pdf
which I interpret as UMASS computer science 5th level half way thru the semester.
and it has a circular reference in the definition of lexical

can't wait to use lexical in a sentence.

Edited by - Sitka on 08/23/2002 14:28:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources