具有多个批次的SQL Server存储过程 - 重用临时表

I have a short SQL script which has been in use for a bit. It reuses a temp table within a script and has been working nicely.

我有一个简短的SQL脚本，已经使用了一段时间。它在脚本中重用了临时表，并且运行良好。

Recently, I decided that I'm going to stick this whole thing into a procedure, though I had a surprise waiting for me - there's a use of the keyword GO in order to divide the script into two batches (that is how I was able to reuse the temp table) - which is why SQL Server is barking at me.

最近，我决定将这一切都放在一个程序中，虽然我有一个惊喜等着我 - 有一个关键字GO的使用，以便将脚本分成两批（这就是我的能力重用临时表） - 这就是SQL Server对我咆哮的原因。

Here is a dumbed down script displaying the functionality of the script:

这是一个显示脚本功能的愚蠢脚本：

DROP TABLE IF EXISTS #temp;
SELECT
    'john' AS first_name
    ,'doe' AS last_name
INTO #temp;

SELECT * FROM #temp
GO 
TRUNCATE TABLE #temp;

DROP TABLE #temp;

SELECT
    'jane' AS first_name
    ,'doe' AS last_name
INTO #temp;

Here's what I tried to do in the procedure, albeit unsuccessfully:

这是我在程序中尝试做的事情，虽然没有成功：

CREATE PROCEDURE #temp_proc 
AS
BEGIN
    DROP TABLE IF EXISTS #temp;
    SELECT
        'john' AS first_name
        ,'doe' AS last_name
    INTO #temp;

    SELECT * FROM #temp
    GO 
    TRUNCATE TABLE #temp;

    DROP TABLE #temp;   

    SELECT
        'jane' AS first_name
        ,'doe' AS last_name
    INTO #temp;
END

Here's the error message I get when I attempt to create the procedure:

这是我尝试创建过程时收到的错误消息：

Msg 102, Level 15, State 1, Procedure #temp_proc, Line 10 [Batch Start Line 0]
Incorrect syntax near '#temp'.

消息102，级别15，状态1，过程#temp_proc，行10 [批处理开始行0]'#temp'附近的语法不正确。

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'END'.

消息102，级别15，状态1，行20'END'附近的语法不正确。

Objective: I'd like to continue reusing the same temp table names, though I would like to stick all of this in a procedure. Any ideas?

目标：我想继续重复使用相同的临时表名称，但我想在程序中坚持所有这些。有任何想法吗？

3 个解决方案

#1

sorry but no - you do not understand GO. It is a keyword that is understood and implemented by an application. It indicates to the application to take all preceding script text (until the beginning of the script or the previous GO) and send it to the db engine for execution. Your procedure creation script is interpreted and executed as 2 separate batches (which is why GO is called a batch separator). The first one is:

对不起但没有 - 你不明白GO。它是应用程序理解和实现的关键字。它向应用程序指示采用所有前面的脚本文本（直到脚本开头或上一个GO）并将其发送到数据库引擎以供执行。您的过程创建脚本将被解释并作为2个单独的批次执行（这就是GO被称为批处理分隔符的原因）。第一个是：

CREATE PROCEDURE #temp_proc 
AS
BEGIN
DROP TABLE IF EXISTS #temp;
SELECT
    'john' AS first_name
    ,'doe' AS last_name
INTO #temp;
SELECT * FROM #temp
GO

Followed by:

其次是：

TRUNCATE TABLE #temp;
DROP TABLE #temp;   
SELECT
    'jane' AS first_name
    ,'doe' AS last_name
INTO #temp;
END

And notice that this is merely creating your procedure. It has nothing to do with how the procedure is executed. There is nothing to leverage here - you cannot divide your procedure definition or execution into batches using "go". So your idea and direction is, quite simply, impossible without a change. Dynamic sql is a possibility - but that is a level of complexity that will challenge and tax you.

请注意，这只是创建您的程序。它与程序的执行方式无关。这里没有什么可以利用的 - 你不能使用“go”将你的程序定义或执行分成批次。所以，如果没有改变，你的想法和方向很简单。动态sql是一种可能性 - 但这是一个复杂程度，将挑战和征税。

#2

You can't use GO in a stored procedure. If you remove it, your code should run as expected. Although there is no need to truncate the table if you drop it later.

您不能在存储过程中使用GO。如果删除它，您的代码应按预期运行。如果稍后删除它，则无需截断表。

#3

Aside from removing the GOs in your code, you can't drop and recreate the same temp table in a stored procedure; SQL Server ignores the drop and attempts to create the same # table in the two SELECT INTO statements.

除了删除代码中的GO之外，您不能删除并在存储过程中重新创建相同的临时表; SQL Server忽略drop并尝试在两个SELECT INTO语句中创建相同的＃表。

#1

CREATE PROCEDURE #temp_proc 
AS
BEGIN
DROP TABLE IF EXISTS #temp;
SELECT
    'john' AS first_name
    ,'doe' AS last_name
INTO #temp;
SELECT * FROM #temp
GO