How do I execute a sql statement through a variable (dyname sql) that tries to do an insert into a variable table?
- by Testifier
If I do what I wanna do with a TEMPORARY TABLE, it works fine:
DECLARE @CTRFR VARCHAR(MAX)
SET @CTRFR = 'select blah blah blah' -- <-- very long select statement. this returns a 0 or some greater number. Please note! --> I NEED THIS NUMBER.
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo][#CTRFRResult]')
AND type IN ( N'U' ) )
DROP TABLE [dbo].[#CTRFRResult]
CREATE TABLE #CTRFRResult
(
CTRFRResult VARCHAR(MAX)
)
SET @CTRFR = 'insert into #CTRFRResult ' + @CTRFR
EXEC(@CTRFR)
The above works fine.
The problem is that several databases are using the same TEMP table. Therefore I need to use a VARIABLE table (instead of a temporary table).
What I have below is not working because it says that the table must be declared.
DECLARE @CTRFRResult TABLE
(
CTRFRResult VARCHAR(MAX)
)
SET @CTRFR = 'insert into @CTRFRResult ' + @CTRFR -- I think the issue is here.
EXEC(@CTRFR)
Setting @CTRFR to 'insert into...' is not working because I'm assuming the table name is out of scope. How would I go about mimicking the temporary table code using a variable table?
