— 判斷存儲過程是否存在
if exists(select name from sysobjects where name='sp_addinfo' )
print '存在 '
else
print '不存在'
— 創建存儲過程
create procedure sp_addinfo
@mid int,
@userid int,
@message varchar(max),
@messagetime datetime
as
INSERT into message (mid,userid,message,messagetime) values(@mid,@userid,@message,@messagetime)
return
— 刪除存儲過程
drop proc sp_addinfo
— 調用存儲過程
exec sp_addinfo '2','1','abc','2018/5/28'
–遊標的使用
–聲明遊標
declare mycursor cursor
for
select mid,userid,message,messagetime from message
–打開遊標
open mycursor
declare @mid int
declare @userid int
declare @message varchar(max)
declare @messagetime datetime
— 從遊標裡取出數據
fetch next from mycursor into @mid,@userid,@message,@messagetime
while(@@fetch_status=0)
begin
print @mid
–用遊標去取下一條記錄
fetch next from mycursor into @mid,@userid,@message,@messagetime
end
–關閉遊標
CLOSE myCursor
–撤銷遊標
deallocate mycursor