Some examples of procudure of Sqlserver 2000
1751 ワード
I have read SQL server 2000 stored procedures HandBook,just do some easy examples of it.here is the code:
I will learn it more :)
create procedure Example_05 @ValIn bigint,@ValOut bigint output
as
begin
if @ValIn > 20
begin
print 'Invalid starting point should be <=20'
return -99
end
declare @WorkValIn bigint, @WorkValOut bigint
if @ValIn != 1
begin
set @WorkValIn = @ValIn -1
print @@NESTLEVEL
exec Example_05 @WorkValIn,@WorkValOut output
set @ValOut = @WorkValOut * @ValIn
end
else
set @ValOut = 1
end
declare @FactIn int,@FactOut int
set @FactIn =6
exec Example_05 @FactIn,@FactOut output
print 'Factorial of ' + convert(varchar(3),@FactIn) +' is '+convert(varchar(20),@FactOut)
create procedure Example_03
as
begin
declare @var1 int,@var2 int
set @var1 = 1
set @var2 = 1
while @var1 <10
begin
if @var2>20
break
set @var1 = @var1+1
set @var2 = @var2+@var2
end
print 'var1='+convert(char(5),@var1) +'and var2='+convert(char(5),@var2)
end
create procedure example_02
as
begin
select product_id,mfr_id,qty_on_hand,
case qty_on_hand
when 0 then 'No qty'
when (select max(qty_on_hand) from [products]) then 'top qty'
else 'aver qty'
end as disc
from [products]
end
I will learn it more :)