第1个回答 2019-12-18
use master
go
/*第一题（1+2+3+……+100），用存储过程*/
if exists(select * from dbo.sysobjects where id = object_id(N'dbo.a1')) --判断是否存在a1存储过程
drop procedure dbo.a1 --如果存在，删除存储过程
go
create procedure a1 --创建存储过程
as
begin
declare @sum int,@i int --定义变量
select @sum=0,@i=1 --为变量赋值
while @i<=100 --循环
begin
set @sum=@sum+@i
set @i=@i+1
end
print @sum --打印结果
return
end
go
print '第一题结果：'
execute a1 --执行a1存储过程
/*第二题(1+3+5+……+99)，用存储过程*/
if exists(select * from dbo.sysobjects where id = object_id(N'dbo.a2'))
drop procedure dbo.a2
go
create procedure a2
as
begin
declare @sum int,@i int
select @sum=0,@i=1
while @i<=100
begin
if @i%2=1
set @sum=@sum+@i
set @i=@i+1
end
print @sum
return
end
go
print '第二题结果:'
execute a2
/*第三题(2+4+6+……+100)，用存储过程*/
if exists(select * from dbo.sysobjects where id = object_id(N'dbo.a3')) --判断存储过程是否存在
drop procedure dbo.a3
go
create procedure a3
as
begin
declare @sum int,@i int
select @sum=0,@i=1
while @i<=100
begin
if @i%2=0
set @sum=@sum+@i
set @i=@i+1
end
print @sum
return
end
go
print '第三题结果：'
execute a3
go
/*第四题(2+4+6+……+100)，用函数*/
if exists(select * from dbo.sysobjects where id = object_id(N'dbo.a4')) --判断是否存在函数a4
drop function dbo.a4 --如果存在，则删除函数a4
go
create function a4(@inputnum int) returns int
as
begin
declare @sum int,@i int
select @sum=0,@i=1
while @i<=100
begin
if @i%@inputnum=0
set @sum=@sum+@i
set @i=@i+1
end
return @sum
end
go
/*第四题*/
print '第四题结果：'
print dbo.a4(3)
go
/*第五题*/
print '第五题结果：'
print dbo.a4(5)
go
/*第六题*/
print '第六题结果：'
print dbo.a4(7)
go
/*第七题*/
print '第七题结果'
print dbo.a4(11)
go
/*第八题，采用带参存储过程*/
if exists(select * from dbo.sysobjects where id = object_id(N'dbo.a8'))
drop procedure dbo.a8
go
create procedure a8 @num int
as
begin
declare @sum int,@i int --定义变量
select @sum=1,@i=1
while @i<=@num
begin
set @sum=@sum*@i
set @i=@i+1
end
print @sum
return
end
go
print '第八题结果:'
execute a8 10 --执行存储过程（传入参数10）
/*第九题，while嵌套*/
if exists(select * from dbo.sysobjects where id = object_id(N'dbo.a9'))
drop procedure dbo.a9
go
create procedure a9 @num int
as
begin
declare @sum int,@i int
select @sum=0,@i=1
while @i<=@num
begin
declare @j int,@temp int
select @j=1,@temp=1
while @j<=@i
begin
set @temp=@temp*@j
set @j=@j+1
end
set @sum=@sum+@temp
set @i=@i+1
end
print @sum
return
end
go
print '第九题结果'
execute a9 10本回答被提问者采纳