标签归档:sql技巧

mssql sqlserver 动态sql超过4000字符的处理方法分享


摘要:
下文讲述动态sql时,如果字符串长度超过4000的处理方法,如下所示:
实验环境:sql server 2012


实现思路:
定义一个nvarchar(max)的字段,然后将动态sql拼接后放入此字段中,既可达到处理动态sql超过4000。
例:

    
declare @SQL nvarchar(max), @SQL1 nvarchar(4000), @SQL2 nvarchar(4000) 

set @SQL1 = N'sql1';  ---超过4000  
set @SQL2 = N'sql2';  ---超过4000  
 
set @SQL = concat(@SQL1, @SQL2);
exec SP_EXECUTESQL @SQL; 

GO

mssql sqlserver sql脚本暂停指定时间运行的方法分享


摘要:
下文讲述sql暂停指定时间运行的方法分享,如下所示:
实验环境:sql server 2008 R2

实现思路:
使用关键字waitfor关键字
例:

print '启动秒数:'+datename(second,getdate())

waitfor delay '00:00:02'
print '暂停2秒'
print ':'+datename(second,getdate()) 

--到22:15:25运行
waitfor time '22:15:25'
print '22:15:25秒运行'

print ':'+datename(second,getdate())
mssql_sqlserver_sql脚本指定时间运行

mssql_sqlserver_sql脚本指定时间运行

mssql sqlserver sql脚本中All,Any,Some关键字的用法简介说明


摘要:
下文讲述sql语句中常出现的All,Any,Some关键字的功能简介,如下所示:
实验环境:sql server 2008 R2


sql All,Any,Some关键字功能简介

All,Any,Some关键字:常结合子查询一起使用,Any和Some关键字具有相同的功能。
All:表示满足子查询中所有信息,则返回true
Any,Some: 表示满足部分信息,则返回true,类似exists和in 关键字

sql All,Any,Some关键字举例说明

 
 
create table [maomao365.comA] (keyId int)  
insert into [maomao365.comA] 
select 20 union all 
select 30    
     
create table [maomao365.comB] (keyId int)  
insert into [maomao365.comB] 
select 10 union all 
select 20 union all
select 30 union all 
select 40  
    
--all 大于A表中所有记录
select * from [maomao365.comB] 
where keyId > all(select keyId from [maomao365.comA] )    --40  
--any 大于任意一条记录,则返回行数据
select * from [maomao365.comB] 
where keyId > any(select keyId from [maomao365.comA] )    --30,40  
select * from [maomao365.comB] 
where keyId > some(select keyId from [maomao365.comA])      --30,40  
    

select * from [maomao365.comB] 
where keyId = all(select keyId from [maomao365.comA] )      
select * from [maomao365.comB] 
where keyId = any(select keyId from [maomao365.comA] )    --20,30  
select * from [maomao365.comB] 
where keyId = some(select keyId from [maomao365.comA])      --20,30  
    
select * from [maomao365.comB] 
where keyId < all(select keyId from [maomao365.comA] )    --10  
select * from [maomao365.comB] 
where keyId < any(select keyId from [maomao365.comA] )    --10,20  
select * from [maomao365.comB] 
where keyId < some(select keyId from [maomao365.comA])      --10,20  
    
select * from [maomao365.comB] 
where keyId <>all (select keyId from [maomao365.comA] )    --10,40  
select * from [maomao365.comB] 
where keyId <>any (select keyId from [maomao365.comA] )    --10,20,30,40  
select * from [maomao365.comB] 
where keyId <>some(select keyId from [maomao365.comA])      --10,20,30,40  

go

truncate table [maomao365.comA]
drop     table [maomao365.comA]


truncate table [maomao365.comB]
drop     table [maomao365.comB]