如何使用exec语法在存储过程中获取第二行集

问题描述

我有一个返回三个行集的存储过程。存储过程是：

I have a stored procedure that returns three rowsets. The stored procedure is:

USE [AVAA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPGetDate]
AS
BEGIN
    SELECT * from tblAVAA;
    SELECT * from tblKit;
    SELECT GETDATE();
END

在exec [dbo]。[SPGetDate]上，它返回三个rowset，这是根据需要，但在另一个存储过程，我只想要第二行集的值，即使用exec [dbo]的tblKit。[SPGetDate] ..是否有任何关键字要添加到 exec [dbo]。[SPGetDate] 这样它只会给我第二行？

请不要给我一个从tblKit直接输入SELECT *的建议;在我需要的SP中，因为这只是我实际要求的一个例子。

任何帮助???

提前谢谢

On exec [dbo].[SPGetDate], it is returning me three rowset which is as needed, but in another stored procedure, i only want value from second rowset, i.e. tblKit using exec [dbo].[SPGetDate].. Is there any key word to be added on exec [dbo].[SPGetDate] so that it will give me only second rowset??
Please do not give me a suggestion of directly entering SELECT * from tblKit; in my required SP because this is just an example of my actual requirement.
Any help???
Thanks in advance

推荐答案

ALTER PROCEDURE [dbo].[SPGetDate]
	@Param1 int, 
	@Param2 int, 
	@Param3 int
AS
BEGIN
	IF @Param1 = 1
		SELECT * from tblAVAA;
	IF @Param2 = 1
		SELECT * from tblKit;
	IF @Param3 = 1
		SELECT GETDATE();
END
GO

这样称为

exec [dbo].[SPGetDate] 0,1,0

或者你可以拥有单个参数并使用按位比较，例如

Or you could have a single parameter and use bitwise comparison e.g.

ALTER PROCEDURE [dbo].[SPGetDate]
    @Param1 int
AS
BEGIN
    IF @Param1 & 1 = 1
        SELECT * from tblAVAA;
    IF @Param1 & 2 = 2
        SELECT * from tblKit;
    IF @Param1 & 4 = 4
        SELECT GETDATE();
END
GO

可以像这（注意变量只是为了明确哪些结果集是必需的）

which could be called like this (note the variables are just to make it obvious which resultsets are required)

declare @RS1 int = 1
declare @RS2 int = 2
declare @RS3 int = 4

exec [dbo].[SPGetDate] @rs2 -- 2nd recordset only

或

declare @reports int = @RS1 + @RS3
exec [dbo].[SPGetDate] @reports --1st and 3rd recordsets only

或者只是丢弃您的其他SP中的其他数据

Alternatively just discard the other data in your other SP

这篇关于如何使用exec语法在存储过程中获取第二行集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！