sql语句中的一些常用语法

以前一直在程序中写sql，最近在sql查询分析器中写sql，发现很多以前写法都行不动，下面做一个记录

1、sql语句中if else

if ...
   begin
       ...
   end
else
   begin
      ....
   end

 

2、把字段赋值给变量：

Declare @selectSql nvarchar(100),@tableCount int
set @selectSql='select @tableCount1=count(1) from ['+@attachementTable+']'
exec sp_executesql @selectSql,N'@tableCount int out',@tableCount output

其中@tableCount output表示输出参数，多个参数写法N'@tableCount int,@param2 nvarchar(100),.....',如果需要输出，参数最后加上out

 

3、sql语句中的循环(游标，话说是什么鬼，不清楚）

DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
WHILE @@FETCH_STATUS=0
        BEGIN
                  SQL语句执行过程... ...
                  FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,... /*这里重复写一次
        END
CLOSE 游标名称
DEALLOCATE 游标名称 (删除游标)

例子：

Declare @Id int,@insertSql nvarchar(1000),@fileName nvarchar(100),@detailId int,@attachementTable nvarchar(100),@tableName VArchar(100),@fieldName VArchar(100),@path nVArchar(100),@thumbnail nVArchar(100),@username nVArchar(100),@state int,@thedate datetime
DECLARE listData CURSOR FOR SELECT [id],[tableName],[fieldName],[path],[thumbnail],[detailId],[username],[state],[thedate]  FROM [pa_attachments]
OPEN listData
fetch next from listData INTO @Id,@tableName,@fieldName,@path,@thumbnail,@detailId,@username,@state,@thedate
WHILE @@FETCH_STATUS=0
Begin
set @attachementTable=@tableName+'_'+@fieldName
iF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'['+@attachementTable+']') AND type in (N'U'))
Begin
set @fileName=reverse(substring(reverse(@path),1,charindex('/',reverse(@path)) - 1)) 
set @insertSql='insert into ['+@attachementTable+']([parentId],[name],[path],[thumbnail],[username],[state],[thedate],[uid]) values('+CONVERT(VARCHAR(10),@detailId)+','''+@fileName+''','''+@path+''','''+@thumbnail+''','''+@username+''','+CONVERT(VARCHAR(10),@state)+','''+CONVERT(VARCHAR(20),@thedate)+''',0)'
execute(@insertSql)
End
else
Begin
update pa_attachments set [state]=-1 where [id]=@Id
End
fetch next from listData INTO @Id,@tableName,@fieldName,@path,@thumbnail,@detailId,@username,@state,@thedate
END
CLOSE listData 
DEALLOCATE listData