在SQL存储过程中如何使用另一个存储过程返回的结果集SQL实例

--创建一个返回结果集的存储过程
create procedure [sp_Test]
@dwUserID as INT,
@strOutMsg NVARCHAR(100)
AS
BEGIN
declare @dwSigninDays INT    
declare @strSigninTime NVARCHAR(22)  
set @dwSigninDays = 1
set @strSigninTime = CONVERT(NVARCHAR(22), GETDATE(), 20);
select @dwUserID as UserID, @dwSigninDays as SigninDays, @strSigninTime as SigninTime
RETURN 0;
END
GO


--以下展示如何在存储过程中如何使用另一个存储过程返回的结果集


--创建临时表
CREATE TABLE #tmp(
UserID int,  
SigninDays INT,   
SigninTime NVARCHAR(20)   
);

declare @strErrorDescribe as nvarchar(100)
--将存储过程结果集插入临时表
INSERT INTO #tmp exec [dbo].[sp_Test] 10001, @strErrorDescribe = @strErrorDescribe OUTPUT


declare @dwUserID INT  
declare @dwSigninDays INT    --签到天数
declare @strSigninTime NVARCHAR(20)   -- 签到时间
  
--从临时表查询结果集
select UserID, SigninDays, SigninTime from #tmp
select @dwUserID = UserID, @dwSigninDays = SigninDays, @strSigninTime = SigninTime from #tmp


--删除临时表
DROP TABLE #tmp


go