Hello
Have searched for quite a bit but haven't found any other way than creating a temp table instead of a table variable, but here goes.
I'd like to declare a table variable using dynamic SQL, the reason being that the variable wil be fairly wide (at least a 100 columns). It will be a join of at least 3 tables. Those also will not always be the same 3 tables. So I was thinking I'd use a dynamic sql statement to declare it and then insert into it normally.
The reason this is a table variable and not a temp table is because it needs to persist after a rollback. Basically I'm trying to make a simple script that will move data into table variable and then to whatever kind of temp table after a rollback. Is there any way to do that?
Regards
Not thought it through fully, but the @TableVariable will be out of scope once your dynamic SQL finishes, I think ??
Yes exactly, that's why I'm asking if there is a a way to keep it or a workaround to delcare a complex table variable for use with rollback.
I think for Scope it will have to be declared ahead of the relevant code that could ROLLBACK and ALSO stay in scope.
I expect you will have to do ALL the code between declaring the @TableVariable and ROLLBACK / etc. IN the dynamic SQL.
Which I think it likely to be a bit of a mare ... so might be better to try alternative solutions first.
We tried to use Table Variables to preserve some LOGGING data (INSERTed into Logging Table during a Transaction) during a ROLLBACK. This needed to be generic (our logging code is centralised), and we gave up (time effort, rather than necessarily "impossible").
Basically we log all SProc calls, and use that for debugging. But if an outer Sproc starts a transaction, calls an inner-Sproc, but then rolls it back we lose the logging information. The rollback is usually because the inner-Sproc returned some sort of error code, and in debugging we would usually find it helpful to see what the Inner-Sproc actually logged ... but we didn't have time to see it through;
Basically all we thought we needed was:
EXEC PreserveLog @LogID = @MyLocalLogID
ROLLBACK SomeSavePointName
EXEC StorePreservedLog
but the @TableVariable created in PreserveLog was out of scope for StorePreservedLog of course ...
Maybe we could have passed the SomeSavePointName label to an all-in-one Preserve-Rollback-and-Store Sproc ...
This worked for me (just an example)
BEGIN TRAN a1;
--magic happens here and I need to save the results.
--myTab is an example of results I need to preserve
if OBJECT_ID(N'myTab') IS NOT NULL
DROP TABLE myTab
SELECT 1 AS 'a' INTO myTab;
--using rollback and another begin in dynamic sql
--because otherwise I get a mismatch between begin and commit
EXEC('DECLARE @myTab TABLE (a INT);
INSERT INTO @myTab(a) SELECT a FROM myTab;
ROLLBACK TRAN a1;
if OBJECT_ID(N''myLogFile'') IS NOT NULL DROP TABLE myLogFile;
SELECT * INTO myLogFile FROM @myTab;BEGIN TRAN a1;');
ROLLBACK TRAN a1;
SELECT * FROM myLogFile;
Thanks for the tip about moving the whole thing (in reality it's just a part of the code that's needed) into dynamic sql.
Regards
Which means now I can declare @mytab based on data from sys.columns etc... Untill I hit a limit on
varchar to build the statements.
EXEC is fine on NVarchar(MAX)
I strongly recommend that you build the SQL in a @Variable and then EXEC that.- if nothing else you get the chance to PRINT (or output/log in some other way) the SQL and that helps with debugging dynamic SQL
Interesting idea to BEGIN TRAN inside the dynamic SQL using the same SavePoint [a1] name as the external transaction 
Yes, I did actually use @variable, but this was just to show it works. As for BEGIN TRAN, couldn't get it to work otherwise since exec was returning mismatch.