存储过程的返回值与结果

所有的存储过程都具有返回值，0代表执行成功，否则代表出错。例如：

USE [Runtime]
GO

DECLARE @return_value int  

EXEC @return_value = [dbo].[GetOtherDayAvgSpeed]
  @p1 = N'CLFJ',
  SELECT 'Return Value' = @return_value

GO

如果希望存储过程能得出某种结果，比如得到一个计算结果，则需要使用OUTPUT参数：

1）创建存储过程：

ALTER PROCEDURE [dbo].[GetOtherDayAvgSpeed]
 -- Add the parameters for the stored procedure here
 @p1 varchar(20) = 0,
 @result float OUTPUT 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(hh,-24,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT @result=round(avg( Value),3)
 FROM History
 WHERE TagName like @p1+'%SR103'
 AND Value>0
 AND wwRetrievalMode = 'Cyclic'
 AND wwCycleCount = 100
 AND wwVersion = 'Latest'
 AND DateTime >= @StartDate
 AND DateTime <= @EndDate
END

2）执行存储过程

 

USE [Runtime]
GO

DECLARE @return_value int,
  @result float

EXEC @return_value = [dbo].[GetOtherDayAvgSpeed]
  @p1 = N'CLFJ',
  @result = @result OUTPUT

SELECT @result as N'@result'

SELECT 'Return Value' = @return_value

GO