sql中游标等一些对象的使用

表结构

create table users
(
 id int,
 name nvarchar(50)
)

用游标遍历表

  declare @id int, @name nvarchar(50)
  declare myCursor cursor for select id,name from users
  open myCursor
  fetch next from myCursor into @id,@name
  while @@fetch_status = 0
  begin  
   if @@error <> 0 break
 处理
 ........
    fetch next from myCursor into @id,@name
  end
  close myCursor
  deallocate myCursor

自定义函数

alter function dbo.up_fun
(
 @name nvarchar(4000)
)
returns nvarchar(4000)
as
begin
 return isnull(@name,'空值')
end

declare @n nvarchar(4000)
print dbo.up_fun(@n)

 

事务

declare @errorSum int
 set @errorSum  = 0
 begin transaction
 --
 set @errorSum = @errorSum + @@error

 if(@errorSum <> 0)
 begin
  raiserror('操作失败!',16,1)
  rollback transaction
 end
 else
 begin
  raiserror('操作成功',16,1)
  commit transaction
 end