问:用sql语句删除数据库中以“xx_”开头的用户表。请问怎么写
答:declare @sql nvarchar(4000)
select @sql=isnull(@sql+char(10),'')+'drop table '+name from sysobjects where type='U' and name like 'xx[_]%'print @sql
exec (@sql)其中红色部分我懂,但是@sql就不知道起什么作用了,能详解一下么?
恕小鸟我比较愚钝...
答:declare @sql nvarchar(4000)
select @sql=isnull(@sql+char(10),'')+'drop table '+name from sysobjects where type='U' and name like 'xx[_]%'print @sql
exec (@sql)其中红色部分我懂,但是@sql就不知道起什么作用了,能详解一下么?
恕小鸟我比较愚钝...
drop table table1
drop table table2
drop table table2
...
用EXEC执行这个字串:exec (@sql)
但是我觉得动态拼接后的语句,DROP语句之间没有空格啊?
declare @name sysname
set @name = 'syscolumns'
exec ('select * from sysobjects where name='''+@name+'''')-------------------------------------------------------------> 数值变量两边加1点
declare @id int
set @id=3
exec ('select * from sysobjects where id='+@id+'')
--> 简化
exec ('select * from sysobjects where id='+@id)-------------------------------------------------------------> 时间变量:
declare @date datetime
set @date = getdate()if object_id('tempdb.dbo.#') is not null drop table #
create table # (date datetime)
insert into # select @date-1
insert into # select @date
insert into # select @date+1select * from # where date<=@date --> 返回2条记录exec ('select * from # where date<='''+@date+'''') --> 返回1条记录,丢失时间精度declare @sql varchar(1000)
set @sql='select * from # where date<='''+convert(varchar,@date,121)+''''
exec (@sql) --> okdeclare @str_date varchar(30)
set @str_date=convert(varchar,@date,121)
exec ('select * from # where date<='''+@str_date+'''') --> ok tooexec ('select * from # where date<='''+convert(varchar,@date,121)+'''') --> error,只接受纯变量,不能潜入函数运算/转换。