How to set Parameters in SQL Server SProc?

Question

I have the following SProc, which works fine for me, but I am trying to create two parameters, to pass in two variables, and I actually have 0 parameters.

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

I would expect to see 2 parameters here, but I see 0.

Answer 1

You need it in the proc declaration...

ALTER PROCEDURE [dbo].[TimeSeries] (@StartDate DATETIME,@EndDate DATETIME)
as...

Answer 2

The syntax is as follows:

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

Drop Table UNION_SUMMARY

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

Answer 3

Because your SP has no parameters. In the context of your Procedure, @StartDate and @EndDate are variables.

To declare a parameter, you need to put them in your CREATE/ALTER clause. For example:

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

    DROP TABLE dbo.UNION_SUMMARY;

    SELECT *
    INTO dbo.UNION_SUMMARY
    ...

END