SQL Server exec关键字

本文介绍了SQL Server exec关键字的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

我尝试了

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

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

解决方案

EXEC关键字告诉SQL您要运行存储过程，函数或字符串. 可以在这里找到语法: msdn.microsoft/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来说，这是结束一批语句的命令.出于多种原因，可以使用它，但是我将在这里开始您的研究: msdn.microsoft/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: msdn.microsoft/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: msdn.microsoft/en-us/library/ms188037.aspx