declare @d1 datetime,@d2 datetime,@sql nvarchar(4000)
set @d1=getdate()
set @d2=dateadd(day,1,@d1)
set @sql='select COUNT(times) from '+DB_NAME()+'_jc.dbo.net_state where (msg like ''%error code%'') AND(id>10) AND (times BETWEEN '''+CONVERT(varchar(20),@d1)+''' and '''+CONVERT(varchar(20),@d2)+''')'
print @sql--结果
/*
select COUNT(times) from test_jc.dbo.net_state where (msg like '%error code%') AND(id>10) AND (times BETWEEN '08 9 2005 10:40AM' and '08 10 2005 10:40AM')*/
set @d1=getdate()
set @d2=dateadd(day,1,@d1)
set @sql='select COUNT(times) from '+DB_NAME()+'_jc.dbo.net_state where (msg like ''%error code%'') AND(id>10) AND (times BETWEEN '''+CONVERT(varchar(20),@d1)+''' and '''+CONVERT(varchar(20),@d2)+''')'
print @sql--结果
/*
select COUNT(times) from test_jc.dbo.net_state where (msg like '%error code%') AND(id>10) AND (times BETWEEN '08 9 2005 10:40AM' and '08 10 2005 10:40AM')*/
DB_NAME()会是null吗?
--我是假设的,只要其中一个变量是null,那么就会有问题,测试:
declare @sql nvarchar(400)
declare @a_add varchar(5)
--set @a_add = ''没有赋值,就是null
set @sql = N'select * from a where a_add=' + @a_add
print @sql
结果
/*
-空白的
*/
declare @sql nvarchar(400)
declare @a_add varchar(5)
set @a_add = 'xx'
set @sql = N'select * from a where a_add=' + @a_add
print @sql
select * from a where a_add=
/*
select * from a where a_add=xx
*/所以,大家以后用来动态sql语句,如果结果是什么都没有,那么可以检查是否有变量是null值