SELECT Statement in T-SQL

[Anon]

I have created a stored procedure and want to use the following queries. It works but the scope of @ variable is lost when use in other query. Actually I want to use the value of @ variable in next INSERT Statement within the same stored procedure.
LIKE

DECLARE @TableName1 AS CHAR(25)
DECLARE @TableName2 AS CHAR(25)

SELECT @TableName1='TABLE1'
SELECT @TableName2='TABLE2'

EXEC ('DECLARE @ AS INTEGER SELECT @=MAX(Col1) FROM ' +@TableName1)

EXEC ('INSERT INTO ' +@TableName2+
'VALUES ('+@)

(Say TABLE2 has only one column)

Please comment it.
Akbar

(http://mondrian.sba.com/forums/Index.cfm

[daus95]

have you try:

EXEC ('INSERT INTO ' +@TableName2+
SELECT MAX(Col1) FROM ' +@TableName1)

this will create a query:
INSERT INTO table1
SELECT MAX(Col1) FROM table2

this query should be work if table1 only had one field,otherwise you have to specified the field:

INSERT INTO table1(field1)
SELECT MAX(Col1) FROM table2

[Anon]

Yes, it works. But what if you need any value other than MAX(col1). I mean value based on WHERE clause, and you have to INSERT two or three values.

Please comment this, thanks
Akbar