????9?? ???α?????洢????
if (object_id('proc_cursor'?? 'P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id?? name?? age from student;
open @cur;
go
--????
declare @exec_cur cursor;
declare @id int??
@name varchar(20)??
@age int;
exec proc_cursor @cur = @exec_cur output;--????洢????
fetch next from @exec_cur into @id?? @name?? @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id?? @name?? @age;
print 'id: ' + convert(varchar?? @id) + '?? name: ' + @name + '?? age: ' + convert(char?? @age);
end
close @exec_cur;
deallocate @exec_cur;--????α?
????10?? ????洢????
---?洢?????row_number?????
if (object_id('pro_page'?? 'P') is not null)
drop proc proc_cursor
go
create proc pro_page
@startIndex int??
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId?? * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1?? 4
--
--????洢????
if (object_id('pro_page'?? 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int??
@pageSize int
)
as
declare @startRow int?? @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *?? row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2?? 2;
· Raiserror
????Raiserror??????????????????????????????????????????????????????????
?????????￡?
????Raiserror({msg_id | msg_str | @local_variable}
????{?? severity?? state}
????[??argument[??…n]]
????[with option[??…n]]
????)
????# msg_id:??sysmessages????????????????????????
????# msg_str:??????????????????????2047???????
????# severity?????????????????????????????????msg_id???????sp_addmessage???????????????????raiserror????????????????sp_addmessage?ж???????????
?????κ???????????0-18??????????????sysadmin????????????????????alter trace??????????????19-25???????????19-25????????????????with log??
????# state??????1??127?????κ???????State??????1??
????raiserror('is error'?? 16?? 1);
????select * from sys.messages;
????--???sysmessages?ж???????
????raiserror(33003?? 16?? 1);
????raiserror(33006?? 16?? 1);