Contents

1 with result sets
2 offset and fetch
3 throw in error handling
4 sequence
5 top over
6 谿瑚襭


[edit]

1 with result sets #

result set data type 譟一 . 
create procedure denali_withresultset
as
select 1 as no,'tsql' type, 'withresultset' as feature union all
select 2 as no,'tsql' type, 'throw' as feature union all
select 3 as no,'tsql' type, 'offset' as feature union all
select 4 as no,'tsql' type, 'sequence' as feature 
go

exec denali_withresultset 
with result sets
(
       (      no int,
              featuretype varchar(50),
              featurename varchar(50)
       )  
) 


--蟆郁骸螳 れ螻 螳 り 
/*
no featuretype featurename
1  tsql        withresultset
2  tsql        throw
3  tsql        offset
4  tsql        sequence
*/

[edit]

2 offset and fetch #

蠏碁 蟆朱. れ 豌 10螳 row 蟆郁骸 讌 螻, 11~ 15蟾讌 5螳 Row襯 襴危.
select productid, name    
from adventureworks.production.product
order by name 
offset 10 rows
fetch next 5 rows only

讀, れ 蟆郁骸螳 螳. 
select productid, name 
from ( 
select row_number() over(order by name) as rowid, productid, name 
from adventureworks.production.product 
) x 
where rowid between 11 and 15 
order by rowid

豌 5螳 row襯 螳語.
select productid, name    
from adventureworks.production.product
order by name 
--offset 10 rows
fetch first 5 rows only
[edit]

3 throw in error handling #

れ螻 螳 讌覃 襯 襴危. 
THROW 50001, 'Error message', 1;

Msg 50001, Level 16, State 1, Line 1 Error message

れ 襯 谿瑚.
begin try 
  select 'using throw'
  select 1 / 0  
end try 
begin catch 
  --throw error 
  throw
end catch

蟆郁骸 れ螻 螳. 
(1 row(s) affected)

 (0 row(s) affected)

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

[edit]

4 sequence #

蟲讌 蟆 讌 覈襯願讌襷, oracle win-back 讌襯 讌碁 蠍危.. lead(), leg() 襷れ伎殊..
create sequence dbo.seq 
as int
start with 1
increment by 1;


SELECT NEXT VALUE FOR dbo.Seq;
SELECT NEXT VALUE FOR dbo.Seq;
SELECT NEXT VALUE FOR dbo.Seq;

--蟆郁骸 谿襦襦 1,2,3 .

れ螻 螳 . 
create table dbo.examp1
(
  seq int not null,
  name varchar(50) not null
);

create table dbo.examp2
(
  seq int not null,
  name varchar(50) not null  
);


insert into dbo.examp1(seq, name) values(next value for dbo.seq, 'tom');
insert into dbo.examp2(seq, name) values(next value for dbo.seq, 'jerry');

select * from examp1 --蟆郁骸 4, tom
select * from examp2 --蟆郁骸 5, jerry

[edit]

5 top over #

ろ 貊瑚?? 讌 誤 覺る. 
SELECT TOP (3) OVER(PARTITION BY custid
ORDER BY val DESC)
orderid, orderdate, custid, empid, val
FROM Sales.OrderValues;


SELECT TOP (3, PARTITION BY custid
ORDER BY val DESC)
orderid, orderdate, custid, empid, val
FROM Sales.OrderValues;


[edit]

6 谿瑚襭 #