sql-server - 如何在 SQL Server SProc 中设置参数？

Question

我有以下 SProc，对我来说效果很好，但我正在尝试创建两个参数来传递两个变量，而我实际上有 0 个参数。

ALTER PROCEDURE [dbo].[TimeSeries]

AS
BEGIN

Drop Table UNION_SUMMARY

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT *
INTO  UNION_SUMMARY
FROM
(
Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_TMP_MG_MORTGAGE_SCHED_HIST_SPLIT' AS TBL From TBL_TMP_MG_MORTGAGE_SCHED_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FMDDATA_HIST_SPLIT' AS TBL From TBL_FMDDATA_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_CDARS_HIST_SPLIT' AS TBL From TBL_FR2052A_CDARS_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_OperationalDepositTag_SPLIT' AS TBL From TBL_FR2052A_OperationalDepositTag_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_FXDH_REVAL_HIST_SPLIT' AS TBL From TBL_FR2052A_FXDH_REVAL_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_MULTI_ACCOUNT_HIST_SPLIT' AS TBL From TBL_MULTI_ACCOUNT_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_MULTI_LD_BALANCE_HIST_SPLIT' AS TBL From TBL_MULTI_LD_BALANCE_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_MULTI_LD_HIST_SPLIT' AS TBL From TBL_MULTI_LD_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_SCHD_HIST_SPLIT' AS TBL From TBL_FR2052A_SCHD_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_MULTI_MG_FUTURE_BALANCE_HIST_SPLIT' AS TBL From TBL_MULTI_MG_FUTURE_BALANCE_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_MULTI_MM_HIST_SPLIT' AS TBL From TBL_MULTI_MM_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_TPOS_HIST_SPLIT' AS TBL From TBL_FR2052A_TPOS_HIST_SPLIT Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_AGG_BOC_HIST' AS TBL From TBL_FR2052A_AGG_BOC_HIST Where AsOfDate Between @StartDate AND @EndDate 
Union Select AsOfDate,Portfolio,PID,Reportable_Amount,'TBL_FR2052A_NB_IU_HIST' AS TBL From TBL_FR2052A_NB_IU_HIST Where AsOfDate Between @StartDate AND @EndDate 
) a

END

我希望在这里看到 2 个参数，但我看到的是 0。

标签： sql-serversql-server-2008

Answer 1

因为你的 SP 没有参数。在您的程序的上下文中，@StartDate和@EndDate是变量。

要声明参数，您需要将它们放在CREATE/ALTER子句中。例如：

ALTER PROCEDURE [dbo].[TimeSeries] @StartDate datetime, @EndDate datetime AS
BEGIN

    DROP TABLE dbo.UNION_SUMMARY;

    SELECT *
    INTO dbo.UNION_SUMMARY
    ...

END