SQL Server exec关键字 [英] SQL Server exec keyword

问题描述

T-SQL中exec关键字的作用是什么?

我尝试了

EXEC sp_rename 'mytable.foo', 'bar', 'column';
/* and */
sp_rename 'mytable.foo', 'bar', 'column';

两个命令产生的结果看似相同.

解决方案

EXEC关键字告诉SQL您要运行存储过程，函数或字符串. 可以在这里找到语法: https://msdn.microsoft.com/zh-我们/library/ms188332.aspx

当您要动态构建SQL语句时，

EXEC对于使用事务SQL是必不可少的.通常的做法是在临时变量字符串中构建sql语句，然后使用EXECUTE关键字执行该语句.

即

DECLARE @SQL varchar(MAX)

DECLARE @DynamicColumnName varchar(20)

DECLARE @SomeDatabase varchar(20)

SELECT @DynamicColumnName =  'Column1'

SELECT @SomeDatabase = 'MyDB'

SELECT @SQL = 'USE ' +@SomeDatabase +
' INSERT INTO #NewTable
  SELECT '+@DyanmicColumnName + 
  ' FROM Mytable'

EXEC (@SQL)

上面将执行以下语句: '使用MyDB 从Mytable插入#NewTable SELECT Column1中

对于GO来说，这是结束一批语句的命令.出于多种原因，可以使用它，但是我将在这里开始您的研究: https://msdn.microsoft.com/zh-CN/library/ms188037.aspx

What is the role of exec keyword in T-SQL?

I tried

EXEC sp_rename 'mytable.foo', 'bar', 'column';
/* and */
sp_rename 'mytable.foo', 'bar', 'column';

Both commands produced seemingly same result.

解决方案

The EXEC keyword tells SQL that you want to run a stored procedure, function or character string. Syntax can be found here: https://msdn.microsoft.com/en-us/library/ms188332.aspx

EXEC is essential for using transact SQL, when you want to built a SQL statement dynamically. Common practice for this is to build a sql statement in a temporary variable string, then use the EXECUTE keyword to execute it.

i.e.

DECLARE @SQL varchar(MAX)

DECLARE @DynamicColumnName varchar(20)

DECLARE @SomeDatabase varchar(20)

SELECT @DynamicColumnName =  'Column1'

SELECT @SomeDatabase = 'MyDB'

SELECT @SQL = 'USE ' +@SomeDatabase +
' INSERT INTO #NewTable
  SELECT '+@DyanmicColumnName + 
  ' FROM Mytable'

EXEC (@SQL)

The above would execute the following statement: 'USE MyDB INSERT INTO #NewTable SELECT Column1 FROM Mytable'

As for GO, it is a command to end a batch of statements. This can be used for a variety of reasons, but I would start your research here: https://msdn.microsoft.com/en-us/library/ms188037.aspx

这篇关于SQL Server exec关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！