调用一个存储过程，该存储过程从一个带有1个参数的存储过程中获取2个参数

I have a stored procedure usp_gethomedata(<current year>) that takes only current year (2012) as parameter and displays data for 01-01-2012 to current date of 2012 i.e 26-11-2012.

我有一个存储过程usp_gethomedata（ <当前年份> ），仅将当前年份（2012年）作为参数，并显示01-01-2012至2012年当前日期的数据，即26-11-2012。

I have another stored procedure that shows similar data in usp_gethomedata(<start date>, <end date>) that takes 2 parameter start and end date ranges from 01-01-1900 to 26-11-2012.

我有另一个存储过程，在usp_gethomedata（ <开始日期> ， <结束日期> ）中显示类似的数据，其中包含2个参数的开始和结束日期范围，从01-01-1900到26-11-2012。

Now, I am not sure how to call the second stored procedure from inside the 1st one so that it display me data for 01-01-2012 to 26-11-2012 provided I have to keep in mind.. that while calling the 1st procedure I can supply only the 2012 i.e the current year as a parameter from my asp .net application.

现在，我不知道如何从第一个存储过程中调用第二个存储过程，以便它显示01-01-2012到26-11-2012的数据，前提是我必须记住..同时调用第一个存储过程过程我只能提供2012年即当前年份作为我的asp .net应用程序的参数。

Please help.

请帮忙。

1 个解决方案

#1

No you cannot create overloaded stored procedure. One will override the other. Though Overloaded function is possible. So, change the inner stored procedure to some different name or the vice versa (i.e. outer stored procedure). Here is an example that will simulate your situation.

不，你不能创建重载的存储过程。一个会覆盖另一个。虽然可以实现重载功能。因此，将内部存储过程更改为某个不同的名称，反之亦然（即外部存储过程）。这是一个模拟您情况的示例。

-- Outer stored procedure

- 外部存储过程

USE [Test]
GO

-- exec [dbo].[USP_GetHomeData] 2012
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetHomeData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_GetHomeData]
GO

CREATE PROCEDURE [dbo].[USP_GetHomeData] 
-- Add the parameters for the stored procedure here
(
    @Year INT
)
AS
BEGIN
        DECLARE @startDate DATETIME='1/1/' + CAST(@Year AS VARCHAR(4))  -- mm/dd/yyyy
        DECLARE @endDate DATETIME=GETDATE() -- mm/dd/yyyy

        SELECT [Date] = DATEADD(Day,Number,@startDate) 
        FROM  master..spt_values  WITH(NOLOCK)
        WHERE Type='P'
        AND DATEADD(day,Number,@startDate) <= @endDate

        exec [dbo].[USP_GetHomeData_Inner] '1/1/1900', @endDate
END

-- Inner stored procedure

- 内部存储过程

USE [Test]
GO

-- exec [dbo].[USP_GetHomeData_Inner] '1/1/1900', '11/25/2012'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetHomeData_Inner]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[USP_GetHomeData_Inner]
GO

CREATE PROCEDURE [dbo].[USP_GetHomeData_Inner] 
-- Add the parameters for the stored procedure here
(
    @startDate DATETIME
    ,@endDate DATETIME
)
AS
BEGIN

        ;WITH Calender AS 
        (
            SELECT @startDate AS CalanderDate
            UNION ALL
            SELECT CalanderDate + 1 FROM Calender
            WHERE CalanderDate + 1 <= @endDate
        )
        SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) 
        FROM Calender WITH(NOLOCK)
        OPTION (MAXRECURSION 0)
END

The result (partial) is as under

结果（部分）如下

Hope this helps.

希望这可以帮助。