006-SQL Server如何创建一个带参数的存储过程并返回多个结果集？

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Proc_Machining_Allocate_Detail_Query_debug
    @sWhere VARCHAR(1000),
    @orderBy VARCHAR(200),
    @PageIndex INT,
    @PageSize INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- 主查询
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'
        SELECT AutoID, PID, MOMID, cDepCode, iDate, fQty, cDeviceCode, EquipName, cTeam, cTeamName, 
               dWorkCreateTime, cMemo, optTime, optUserID, optUserName, ProcessType, MOM_MainNo, 
               cInvCode, cInvName, cInvStd, ProcedureCode, ProcedureName, bClose, cInvDefine8, 
               cDefine8, IsHide, fQty, iSerialNo
        FROM dbo.V_Machining_Allocate_Detail WITH(NOLOCK)
        WHERE 1=1 ' + @sWhere + ' ' + @orderBy + ' 
        OFFSET ' + CAST((@PageIndex - 1) * @PageSize AS NVARCHAR) + ' ROWS 
        FETCH NEXT ' + CAST(@PageSize AS NVARCHAR) + ' ROWS ONLY;';

    -- 执行动态SQL
    EXEC sp_executesql @SQL;
END
GO

-- 创建临时表
CREATE TABLE #cTmp(
    AutoID INT,
    PID INT,
    MOMID INT,
    cDepCode VARCHAR(30),
    cDepName NVARCHAR(30),
    iDate DATE,
    fQty DECIMAL(13,3),
    cDeviceCode VARCHAR(30),
    EquipName NVARCHAR(30),
    cTeam VARCHAR(30),
    cTeamName NVARCHAR(30),
    dWorkCreateTime DATETIME,
    cMemo NVARCHAR(120),
    optTime DATETIME,
    optUserID VARCHAR(30),
    optUserName NVARCHAR(30),
    ProcessType INT,
    MOM_MainNo VARCHAR(30),
    cInvCode VARCHAR(30),
    cInvName NVARCHAR(30),
    cInvStd NVARCHAR(255),
    ProcedureCode VARCHAR(30),
    ProcedureName NVARCHAR(30),
    bClose INT,
    cInvDefine8 VARCHAR(30),
    cDefine8 VARCHAR(30),
    IsHide INT,
    IssueQty DECIMAL(13,3),
    iSerialNo INT
    -- 注意：以下列未在查询中出现，需要默认值或之后填充
    -- ,OKQty INT DEFAULT 0
    -- ,NGQty INT DEFAULT 0
    -- ,preQty DECIMAL(18,6) DEFAULT 0
    -- ,thisQty DECIMAL(18,6) DEFAULT 0
    -- ,OkJobQty DECIMAL(18,6) DEFAULT 0
    -- ,preID INT DEFAULT 0
    -- ,ReportQty INT DEFAULT 0
    -- ,RestQty INT DEFAULT 0
    -- ,ScrapQty INT DEFAULT 0
);

-- 插入数据
INSERT INTO #cTmp(
    AutoID, PID, MOMID, cDepCode, iDate, fQty, cDeviceCode, EquipName, cTeam, cTeamName,
    dWorkCreateTime, cMemo, optTime, optUserID, optUserName, ProcessType, MOM_MainNo,
    cInvCode, cInvName, cInvStd, ProcedureCode, ProcedureName, bClose, cInvDefine8,
    cDefine8, IsHide, IssueQty, iSerialNo
)
SELECT 
    AutoID, PID, MOMID, cDepCode, iDate, fQty, cDeviceCode, EquipName, cTeam, cTeamName,
    dWorkCreateTime, cMemo, optTime, optUserID, optUserName, ProcessType, MOM_MainNo,
    cInvCode, cInvName, cInvStd, ProcedureCode, ProcedureName, bClose, cInvDefine8,
    cDefine8, IsHide, fQty, iSerialNo -- 注意：这里的fQty被重复使用为IssueQty，如果IssueQty有不同数据，请替换
FROM dbo.V_Machining_Allocate_Detail WITH(NOLOCK)
WHERE 1=1 
AND bClose = '1' 
AND iDate BETWEEN '2024-07-17' AND '2024-07-22' 
AND ProcedureName LIKE '%阀焊接1%' 
AND cDepCode = '1014' 
ORDER BY dWorkCreateTime DESC 
OFFSET 0 ROWS 
FETCH NEXT 30 ROWS ONLY;