由 amtd 发布于: 2001-02-15 10:00

用变量替代表名，列名的SQL命令怎样写？

(佚名)

答：可在帮助里查找SQL的EXEC命令信息。基本上是要运行一个已经定义好的程序。

举一个简短的例子：从一个表中选择一列

USE pubs

go


DECLARE @str varchar(255)

DECLARE @columnname varchar(30)


SELECT @columnname='au_lname'


SELECT @str = 'SELECT ' + @columnname + ' FROM authors'


EXEC (@str)

另一个例子是在线书籍中的。这个例子显示了EXECUTE命令是怎样动态处理带变量的字符串。这个例子创建一个光标（tables_cursor)来保持所有用户定义表（TYPE=‘U’）的列单。

DECLARE tables_cursor CURSOR

FOR

SELECT name FROM sysobjects WHERE type = 'U'


OPEN tables_cursor

DECLARE @tablename varchar(30)

FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@fetch_status -1)

BEGIN

/*

A @@fetch_status of -2 means that the row has been deleted.

No need to test for this as the result of this loop is to

drop all user-defined tables.

*/

EXEC ("DROP TABLE " @tablename)

FETCH NEXT FROM tables_cursor INTO @tablename

END

PRINT "All user-defined tables have been dropped from the database."

DEALLOCATE tables_cursor


__________________