灭绝数据Curry面装有客商表的多少

mssql server把数据库教程全体客户存款和储蓄进程的EXEC权限赋另一剧中人物

金沙中心城 1

declare @name varchar(8000)
declare cursor_a cursor for select  name from sysobjects  where
xtype=’U’
open  cursor_a
fetch next from cursor_a into @name
while @@fetch_status=0
begin 
  exec(‘delete from  ‘+@name)
  fetch next  from cursor_a into @name
end
close cursor_a
deallocate cursor_a

$sql=create procedure sp_GrantProce(@username varchar(40)) as begin
declare @user varchar(20),@name varchar(40) declare t_cursor cursor
for select user_name(uid) as users,name from sysobjects where (xtype=
‘P ‘ or xtype= ‘X ‘) and status 0 open t_cursor fetch next from
t_cursor into @user,@name while @@fetch_status=0 begin exec( ‘grant
execute on ‘+@user+ ‘. ‘+@name+ ‘ to ‘+@username) fetch next from
t_cursor into @user,@name end close t_cursor deallocate t_cursor
end go;

select 3,121,’t’ 

declare @deptid int,@username varchar(20)
–定义游标
declare Select_cursor cursor for
        select deptid,username from #Temp1
open Select_cursor
金沙中心城,fetch next from Select_cursor into @deptid,@username    –提取操作的列数据放到局地变量中
while @@fetch_status=0      –重返被 FETCH 语句奉行的尾声游标的场合
/*
@@FETCH_STATUS =0          FETCH 语句成功
@@FETCH_STATUS =-1 FETCH 语句战败或此行不在结果聚焦
@@FETCH_STATUS =-2 被提取的行不设有
*/
        begin
                  –当表#Temp2列deptid存在同样的数据时,就径直在列username上平添@username值
                  if(exists(select * from #Temp2 where deptid=@deptid )) 
                          update #Temp2 set username=username +@username where deptid=@deptid
                  else 
                  –插入新数据
                          insert into #Temp2 select @deptid,@username
                  fetch next from Select_cursor into @deptid,@username
        end
close Select_cursor      
deallocate Select_cursor
select * from #Temp2 –测验结果
Drop table #Temp1,#Temp2

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

CopyRight © 2015-2020 金沙中心城 All Rights Reserved.
网站地图xml地图