if object_id('pro_test') is not null drop proc pro_test go create proc pro_test ( @input int ) as declare @sum int set @sum=0 while @sum<=100 begin set @sum=@sum+@input set @input=@input+1 end set @sum=@sum-@input print @sum go exec pro_test 18不知道你具体要干什么
管他什么题 写了再说 最近不写SQL 语句 都不怎么熟练了 先练练手
--负数木有考虑进去--if object_id('pro_Accumulation') is not null drop proc pro_Accumulation go create proc pro_Accumulation ( @inputNumber int ) as declare @sum int set @sum=@inputNumber if @inputNumber>100 begin set @sum=@inputNumber set @inputNumber=null end if @inputNumber>1 begin if @inputNumber%2=0 set @sum=(@inputNumber/2)*(1+@inputNumber)--(1+2+3+4+5+....+100 => 100/2*(1+100)) else set @sum=(@inputNumber/2)*(1+@inputNumber)+@inputNumber/2+1 end if @inputNumber is null print '输入参数:'+convert(nvarchar(100),@sum)+'大于100' else print @sum /* exec pro_Accumulation 0 --输出:0 exec pro_Accumulation 1 --输出:1 exec pro_Accumulation 70 --输出:2485 exec pro_Accumulation 102 --输出:输入参数:102大于100 */
SELECT SUM(NUMBER) FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND @参数 AND @参数<10
嘿嘿! 高手都爱用 MASTER..SPT_VALUES
IF OBJECT_ID('proc_test') IS NOT NULL DROP PROC proc_test GOCREATE PROC proc_test( @var INT) AS DECLARE @total INT BEGIN SELECT @total = Sum(NUMBER) FROM master..spt_values WHERE TYPE = 'P' AND NUMBER <= @var PRINT @total END GOEXEC proc_test @var='70'
IF OBJECT_ID('proc_test') IS NOT NULL DROP PROC proc_test GOCREATE PROC proc_test( @var INT) AS DECLARE @total INT BEGIN SELECT @total = Sum(NUMBER) FROM master..spt_values WHERE TYPE = 'P' AND NUMBER>=1 AND NUMBER<= @var PRINT @total END GOEXEC proc_test @var='70'
if object_id('pro_test')is not null drop proc pro_test go create proc pro_test ( @input smallint ) as if @input<0 begin select ltrim(@input)+'为负数,不合法' as Result end else if @input>=100 begin select ltrim(@input)+'不能超过100(含100)' as Result end else begin select '结果为:'+ltrim(SUM(number)) as Result from master..spt_values where type='p' and number between 0 and 70 end go--测试验证exec pro_test -20 /* Result -------------------- -20为负数,不合法(1 行受影响) */exec pro_test 104/* Result ------------------------ 104不能超过100(含100)(1 行受影响) */exec pro_test 23/* Result -------------------- 结果为:2485(1 行受影响) */
忽略创建存储过程的语句,这个太简单直接来主体部分Declare @i int set @i = 1 While @i < 100 Begin Set @i = @i + 1 End
drop proc pro_test
go
create proc pro_test
(
@input int
)
as
declare @sum int
set @sum=0
while @sum<=100
begin
set @sum=@sum+@input
set @input=@input+1
end
set @sum=@sum-@input
print @sum
go
exec pro_test 18不知道你具体要干什么
管他什么题 写了再说 最近不写SQL 语句 都不怎么熟练了 先练练手
go
create proc pro_Accumulation
(
@inputNumber int
)
as
declare @sum int
set @sum=@inputNumber
if @inputNumber>100
begin
set @sum=@inputNumber
set @inputNumber=null
end
if @inputNumber>1
begin
if @inputNumber%2=0
set @sum=(@inputNumber/2)*(1+@inputNumber)--(1+2+3+4+5+....+100 => 100/2*(1+100))
else
set @sum=(@inputNumber/2)*(1+@inputNumber)+@inputNumber/2+1
end
if @inputNumber is null
print '输入参数:'+convert(nvarchar(100),@sum)+'大于100'
else
print @sum
/*
exec pro_Accumulation 0 --输出:0
exec pro_Accumulation 1 --输出:1
exec pro_Accumulation 70 --输出:2485
exec pro_Accumulation 102 --输出:输入参数:102大于100
*/
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND NUMBER BETWEEN 1 AND @参数 AND @参数<10
嘿嘿! 高手都爱用 MASTER..SPT_VALUES
DROP PROC proc_test GOCREATE PROC proc_test(
@var INT)
AS
DECLARE
@total INT
BEGIN
SELECT @total = Sum(NUMBER)
FROM master..spt_values
WHERE TYPE = 'P'
AND NUMBER <= @var
PRINT @total
END
GOEXEC proc_test @var='70'
DROP PROC proc_test GOCREATE PROC proc_test(
@var INT)
AS
DECLARE
@total INT
BEGIN
SELECT @total = Sum(NUMBER)
FROM master..spt_values
WHERE TYPE = 'P'
AND NUMBER>=1 AND NUMBER<= @var
PRINT @total
END
GOEXEC proc_test @var='70'
if object_id('pro_test')is not null
drop proc pro_test
go
create proc pro_test
(
@input smallint
)
as
if @input<0
begin
select ltrim(@input)+'为负数,不合法' as Result
end
else if @input>=100
begin
select ltrim(@input)+'不能超过100(含100)' as Result
end
else
begin
select
'结果为:'+ltrim(SUM(number)) as Result
from
master..spt_values
where
type='p' and number between 0 and 70
end
go--测试验证exec pro_test -20
/*
Result
--------------------
-20为负数,不合法(1 行受影响)
*/exec pro_test 104/*
Result
------------------------
104不能超过100(含100)(1 行受影响)
*/exec pro_test 23/*
Result
--------------------
结果为:2485(1 行受影响)
*/
set @i = 1
While @i < 100
Begin
Set @i = @i + 1
End