SQL 有没有办法在整个过程中保持变量？

Question

提问by NitroxDM

Is there a way to persist a variable across a go?

有没有办法在整个过程中保持变量？

Declare @bob as varchar(50);
Set @bob = 'SweetDB'; 
GO
USE @bob  --- see note below
GO
INSERT INTO @bob.[dbo].[ProjectVersion] ([DB_Name], [Script]) VALUES (@bob,'1.2')

See this SOquestion for the 'USE @bob' line.

请参阅“USE @bob”行的这个SO问题。

Answer 1

采纳答案by Guffa

The gocommand is used to split code into separate batches. If that is exactly what you want to do, then you should use it, but it means that the batches are actually separate, and you can't share variables between them.

该go命令用于将代码拆分为单独的批次。如果这正是您想要做的，那么您应该使用它，但这意味着批次实际上是分开的，并且您不能在它们之间共享变量。

In your case the solution is simple; you can just remove the gostatements, they are not needed in that code.

在您的情况下，解决方案很简单；您可以删除go语句，该代码中不需要它们。

Side note: You can't use a variable in a usestatement, it has to be the name of a database.

旁注：您不能在use语句中使用变量，它必须是数据库的名称。

Answer 2

回答by RBarryYoung

Use a temporary table:

使用临时表：

CREATE TABLE #variables
    (
    VarName VARCHAR(20) PRIMARY KEY,
    Value VARCHAR(255)
    )
GO

Insert into #variables Select 'Bob', 'SweetDB'
GO

Select Value From #variables Where VarName = 'Bob'
GO

DROP TABLE #variables
go

Answer 3

回答by Matt Vukomanovic

I prefer the thisanswer from this question Global Variables with GO

我更喜欢这个问题的这个答案 Global Variables with GO

Which has the added benefit of being able to do what you originally wanted to do as well.

这还有一个额外的好处，那就是能够做你最初想做的事情。

The caveat is that you need to turn on SQLCMD mode (under Query->SQLCMD) or turn it on by default for all query windows (Tools->Options then Query Results->By Default, open new queries in SQLCMD mode)

需要注意的是，您需要打开 SQLCMD 模式（在 Query->SQLCMD 下）或默认为所有查询窗口打开它（工具->选项，然后查询结果->默认，在 SQLCMD 模式下打开新查询）

Then you can use the following type of code (completely ripped off from that same answer by Oscar E. Fraxedas Tormo)

然后你可以使用以下类型的代码（完全从Oscar E. Fraxedas Tormo 的同一个答案中扯掉）

--Declare the variable
:setvar MYDATABASE master
--Use the variable
USE $(MYDATABASE);
SELECT * FROM [dbo].[refresh_indexes]
GO
--Use again after a GO
SELECT * from $(MYDATABASE).[dbo].[refresh_indexes];
GO

Answer 4

回答by DanteTheSmith

If you are using SQL Server you can setup global variables for entire scripts like:

如果您使用 SQL Server，您可以为整个脚本设置全局变量，例如：

:setvar sourceDB "lalalallalal"

and use later in script as:

稍后在脚本中使用：

$(sourceDB)

Make sure SQLCMD mode is on in Server Managment Studi, you can do that via top menu Click Query and toggle SQLCMD Mode on.

确保在 Server Managment Studi 中启用 SQLCMD 模式，您可以通过顶部菜单单击查询并打开 SQLCMD 模式来执行此操作。

More on topic can be found here: MS Documentation

可以在此处找到有关主题的更多信息： MS 文档

Answer 5

回答by Remco Nonhebel

Temp tables are retained over GO statements, so...

临时表保留在 GO 语句上，所以...

SELECT 'value1' as variable1, 'mydatabasename' as DbName INTO #TMP

-- get a variable from the temp table
DECLARE @dbName VARCHAR(10) = (select top 1 #TMP.DbName from #TMP)
EXEC ('USE ' + @dbName)
GO

-- get another variable from the temp table
DECLARE @value1 VARCHAR(10) = (select top 1 #TMP.variable1 from #TMP)

DROP TABLE #TMP

It's not pretty, but it works

它不漂亮，但它有效

Answer 6

回答by Contango

Create your own stored procedures which save/load to a temporary table.

创建您自己的存储过程，用于保存/加载到临时表。

MyVariableSave   -- Saves variable to temporary table. 
MyVariableLoad   -- Loads variable from temporary table.

Then you can use this:

然后你可以使用这个：

print('Test stored procedures for load/save of variables across GO statements:')

declare @MyVariable int = 42
exec dbo.MyVariableSave @Name = 'test', @Value=@MyVariable
print('  - Set @MyVariable = ' + CAST(@MyVariable AS VARCHAR(100)))

print('  - GO statement resets all variables')
GO -- This resets all variables including @MyVariable

declare @MyVariable int
exec dbo.MyVariableLoad 'test', @MyVariable output
print('  - Get @MyVariable = ' + CAST(@MyVariable AS VARCHAR(100)))

Output:

输出：

Test stored procedures for load/save of variables across GO statements:
  - Set @MyVariable = 42
  - GO statement resets all variables
  - Get @MyVariable = 42

You can also use these:

您还可以使用这些：

exec dbo.MyVariableList       -- Lists all variables in the temporary table.
exec dbo.MyVariableDeleteAll  -- Deletes all variables in the temporary table.

Output of exec dbo.MyVariableList:

的输出exec dbo.MyVariableList：

Name    Value
test    42

It turns out that being able to list all of the variables in a table is actually quite useful. So even if you do not load a variable later, its great for debugging purposes to see everything in one place.

事实证明，能够列出表中的所有变量实际上非常有用。因此，即使您稍后不加载变量，在一个地方查看所有内容也非常适合调试。

This uses a temporary table with a ##prefix, so it's just enough to survive a GO statement. It is intended to be used within a single script.

这使用了一个带有##前缀的临时表，所以它足以在 GO 语句中存活下来。它旨在在单个脚本中使用。

And the stored procedures:

和存储过程：

-- Stored procedure to save a variable to a temp table.
CREATE OR ALTER PROCEDURE MyVariableSave 
    @Name varchar(255),
    @Value varchar(MAX)
WITH EXECUTE AS CALLER
AS  
BEGIN
    SET NOCOUNT ON
    IF NOT EXISTS (select TOP 1 * from tempdb.sys.objects where name = '##VariableLoadSave')
    BEGIN
        DROP TABLE IF EXISTS ##VariableLoadSave
        CREATE TABLE ##VariableLoadSave
        (
            Name varchar(255),
            Value varchar(MAX)
        )
    END
    UPDATE ##VariableLoadSave SET Value=@Value WHERE Name=@Name
    IF @@ROWCOUNT = 0
        INSERT INTO ##VariableLoadSave SELECT @Name, @Value
END
GO
-- Stored procedure to load a variable from a temp table.
CREATE OR ALTER PROCEDURE MyVariableLoad 
    @Name varchar(255),
    @Value varchar(MAX) OUT
WITH EXECUTE AS CALLER
AS  
BEGIN
    IF EXISTS (select TOP 1 * from tempdb.sys.objects where name = '##VariableLoadSave')
    BEGIN
        IF NOT EXISTS(SELECT TOP 1 * FROM ##VariableLoadSave WHERE Name=@Name)
        BEGIN
            declare @ErrorMessage1 as varchar(200) = 'Error: cannot find saved variable to load: ' + @Name
            raiserror(@ErrorMessage1, 20, -1) with log
        END

        SELECT @Value=CAST(Value AS varchar(MAX)) FROM ##VariableLoadSave
        WHERE Name=@Name
    END
    ELSE
    BEGIN
        declare @ErrorMessage2 as varchar(200) = 'Error: cannot find saved variable to load: ' + @Name
        raiserror(@ErrorMessage2, 20, -1) with log
    END
END
GO
-- Stored procedure to list all saved variables.
CREATE OR ALTER PROCEDURE MyVariableList
WITH EXECUTE AS CALLER
AS  
BEGIN
    IF EXISTS (select TOP 1 * from tempdb.sys.objects where name = '##VariableLoadSave')
    BEGIN
        SELECT * FROM ##VariableLoadSave
        ORDER BY Name
    END
END
GO
-- Stored procedure to delete all saved variables.
CREATE OR ALTER PROCEDURE MyVariableDeleteAll
WITH EXECUTE AS CALLER
AS  
BEGIN
    DROP TABLE IF EXISTS ##VariableLoadSave
    CREATE TABLE ##VariableLoadSave
    (
        Name varchar(255),
        Value varchar(MAX)
    )
END

Answer 7

回答by shahkalpesh

Not sure, if this helps

不确定，如果这有帮助

declare @s varchar(50)
set @s='Northwind'

declare @t nvarchar(100)
set @t = 'select * from ' + @s + '.[dbo].[Customers]'

execute sp_executesql @t

Answer 8

回答by yzorg

If you just need a binary yes/no (like if a column exists) then you can use SET NOEXEC ONto disable execution of statements. SET NOEXEC ONworks across GO (across batches). But remember to turn EXEC back onwith SET NOEXEC OFFat the end of the script.

如果您只需要一个二进制是/否（如存在列），那么您可以使用SET NOEXEC ON来禁用语句的执行。 SET NOEXEC ON跨 GO（跨批次）工作。但记得要打开EXEC回用SET NOEXEC OFF在脚本的结尾。

IF COL_LENGTH('StuffTable', 'EnableGA') IS NOT NULL
    SET NOEXEC ON -- script will not do anything when column already exists

ALTER TABLE dbo.StuffTable ADD EnableGA BIT NOT NULL CONSTRAINT DF_StuffTable_EnableGA DEFAULT(0)
ALTER TABLE dbo.StuffTable SET (LOCK_ESCALATION = TABLE)
GO
UPDATE dbo.StuffTable SET EnableGA = 1 WHERE StuffUrl IS NOT NULL
GO
SET NOEXEC OFF

This compiles statements but does not execute them. So you'll still get "compile errors" if you reference schema that doesn't exist. So it works to "turn off" the script 2nd run (what I'm doing), but does not work to turn off parts of the script on 1st run, because you'll still get compile errors if referencing columns or tables that don't exist yet.

这会编译语句但不执行它们。因此，如果您引用不存在的架构，您仍然会收到“编译错误”。所以它可以“关闭”第二次运行的脚本（我正在做的事情），但不能在第一次运行时关闭部分脚本，因为如果引用不运行的列或表，你仍然会得到编译错误'还不存在。

Answer 9

回答by Viv Pathania

You can make use of NOEXEC follow he steps below:

您可以按照以下步骤使用 NOEXEC：

Create table

创建表

#temp_procedure_version(procedure_version varchar(5),pointer varchar(20))

insert procedure versions and pointer to the version into a temp table #temp_procedure_version

将过程版本和指向版本的指针插入临时表 #temp_procedure_version

--example procedure_version pointer

--example procedure_version 指针

insert into temp_procedure_versionvalues(1.0,'first version')

插入temp_procedure_version值（1.0，'第一个版本'）

insert into temp_procedure_versionvalues(2.0,'final version')

插入temp_procedure_version值（2.0，'最终版本'）

then retrieve the procedure version, you can use where condition as in the following statement

然后检索过程版本，您可以使用以下语句中的 where 条件

Select @ProcedureVersion=ProcedureVersionfrom #temp_procedure_versionwhere pointer='first version'

@ProcedureVersion=ProcedureVersion从#temp_procedure_version哪里选择pointer='first version'

IF (@ProcedureVersion='1.0')
    BEGIN
    SET NOEXEC OFF  --code execution on 
    END
ELSE
    BEGIN 
    SET NOEXEC ON  --code execution off
    END

--insert procedure version 1.0 here

--在此处插入程序版本 1.0

Create procedure version 1.0 as.....

创建程序版本 1.0 为.....

SET NOEXEC OFF -- execution is ON

Select @ProcedureVersion=ProcedureVersionfrom #temp_procedure_versionwhere pointer='final version'

@ProcedureVersion=ProcedureVersion从#temp_procedure_version何处选择指针='最终版本'

IF (@ProcedureVersion='2.0')
    BEGIN
    SET NOEXEC OFF  --code execution on 
    END
ELSE
    BEGIN 
    SET NOEXEC ON  --code execution off
    END

Create procedure version 2.0 as.....

创建程序版本 2.0 为.....

SET NOEXEC OFF -- execution is ON

--drop the temp table

--删除临时表

Drop table #temp_procedure_version

删除表 #temp_procedure_version

SQL 有没有办法在整个过程中保持变量？

提问by NitroxDM

采纳答案by Guffa

回答by RBarryYoung

回答by Matt Vukomanovic

回答by DanteTheSmith

回答by Remco Nonhebel

回答by Contango

回答by shahkalpesh

回答by yzorg

回答by Viv Pathania

相关推荐

最近更新

标签

SQL 有没有办法在整个过程中保持变量？

提问by NitroxDM

采纳答案by Guffa

回答by RBarryYoung

回答by Matt Vukomanovic

回答by DanteTheSmith

回答by Remco Nonhebel

回答by Contango

回答by shahkalpesh

回答by yzorg

回答by Viv Pathania

相关推荐

将 FOR XML AUTO 结果保存到 SQL 中的变量

获取 SQL Server 中列的频率

如何在 Excel 工作表中的命名范围上运行 SQL 语句？

SQL 从员工表中选择前 10 名薪水

相关推荐

最近更新

标签