use pubs
/*pubs 数据库*/
go
declare @sql varchar(8000)
set @sql='select '
select @[email protected]+quotename(Names)+'='+(Names)+' ,'
from (SELECT Rtrim(column_name) as Names FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'authors' )a
select @sql=left(@sql, len(@sql)-1)
exec(@sql)
消息 207，级别 16，状态 1，第 1 行
列名 'au_id' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'au_lname' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 'au_fname' 无效。
------解决方案--------------------
--如何将exec执行结果放入变量中？
declare @num int, @sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,[email protected] int output',@num output
select @num
------解决方案--------------------
- SQL code
--tryuse pubs /*pubs 数据库*/ go declare @sql varchar(8000) set @sql='select ' select @[email protected]+quotename(Names)+'=['+Names+'] ,' from (SELECT Rtrim(column_name) as Names FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'authors' ) a select @sql=left(@sql, len(@sql)-1) exec(@sql)
------解决方案--------------------
- SQL code
create table T(id int) insert T select 1 insert T select 2 insert T select 3 insert T select 4 insert T select 5 declare @sql varchar(8000) set @sql='select ' select @[email protected]+quotename(id)+'='''+rtrim(id)+''',' from T select @sql=left(@sql,len(@sql)-1) exec(@sql) drop table T /*1 2 3 4 5---- ---- ---- ---- ----1 2 3 4 5(1 行受影响)*/
------解决方案--------------------
- SQL code
use pubs /*pubs 数据库*/ go declare @sql varchar(8000) set @sql='select ' select @[email protected]+ ''''+quotename(names)+''' as ' + quotename(names) + ',' from (select rtrim(column_name) as names from information_schema.columns where table_name = 'authors' ) a select @sql=left(@sql, len(@sql)-1) exec(@sql)