CODEWITHSUNDEEP
sql-server
mark
mark

Reputation: 62732

Simulating Sql Server GO statement with EXEC

I have code that given a SQL with the GO statements transforms it to a single SQL statement without the GO statements by using the following logic:

"EXEC('" + sql.Replace("'", "''").Replace("GO", "');EXEC('") + "')"

Of course, the actual logic a bit more robust, but the spirit is the same.

However, GO is not equivalent to EXEC. For example, the following code works:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
SELECT 1 A INTO #tmp
GO
SELECT * FROM #tmp

And returns:

enter image description here

However, the version with EXEC does not work:

EXEC('
IF OBJECT_ID(''tempdb..#tmp'') IS NOT NULL DROP TABLE #tmp
SELECT 1 A INTO #tmp
');EXEC('
SELECT * FROM #tmp
')

The result is:

enter image description here

Is there a way to eliminate the GO statements so that the resulting SQL can be run in ADO.NET with one SQL command?

EDIT 1

I apologize, if my post is confusing for some. I am not looking for ways to create table UDTs or temporary tables.

I am using ADO.NET to talk to a Sql Server database. Specifically, I am using the SqlCommand class to run a SQL on the server. If that SQL contains the GO statement, then it is an invalid Sql, because GO is not a Sql keyword, it is used to delimit batches when ran in the SSMS. So, there are two ways to deal with it:

  1. Split the SQL using the GO keyword as a delimited and run each chunk separately. So, if there are N batches, then there will be N SqlCommand API calls to run the respective batches.
  2. Replace GO with EXEC as I have described. In this case, there is only one SqlCommand, but EXEC and GO are not equivalent.

So, I am asking if there is another way to respect the GO statement in ADO.NET while issuing just one SqlCommand API call to the Sql Server.

Upvotes: 0

Views: 609

Answers (2)

SMor
SMor

Reputation: 2862

Short answer - NO. If a script is written as multiple batches, then you must execute them as multiple batches. You cannot "simulate" the effect of batch separation within one batch execution without changing the tsql used drastically - and that would involve writing to code to interpret and then rewrite the script.

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Dynamic query will not store the temp table in the temp database therefore you don't need to drop it.

IN SSMS

Try this and should give you what you want.

EXEC('SELECT 1 A INTO #tmp;
    SELECT * FROM #tmp');

But I think you are leaning towards the use of user-defined table types https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx

where it uses in-memory table that does not consume space and need not to be dropped.

Upvotes: 0

Related Questions