T-SQL - dynamic parameter in stored procedere [duplicate]

Question

Is there a way to use dynamic date parameter in a stored procedure between CREATE PROCEDURE and BEGIN?

CREATE PROCEDURE dbo.MYSP 
    @StartDate DATETIME = GETDATE(),
    @EndDate DATETIME = GETDATE() - 1
BEGIN
AS

Is this possible?

BEGIN should start after AS not before and are you sure EndDate is one day before the StartDate? — Zeki Gumus
– Zeki Gumus, Commented Nov 16, 2018 at 20:31
this is the start of my parameter declaration CREATE PROCEDURE dbo.Tableau_Test_SP_Parameter -- Add the parameters for the stored procedure here @StartDate DATETIME=CONVERT(DATE,GETDATE(),101) AS BEGIN but it is giving me a syntax error on CONVERT and GETDATE() — Arsee
– Arsee, Commented Nov 16, 2018 at 21:31

Ilyes · Accepted Answer · 2018-11-16 20:28:34Z

2

You can't add GETDATE() default parameter to stored procedure. Instead of this you can do this :

DROP PROCEDURE dbo.MYSP 
GO
CREATE PROCEDURE dbo.MYSP 
    @StartDate DATETIME = NULL,
    @EndDate DATETIME = NULL
AS
BEGIN
IF @StartDate IS NULL SET @StartDate=GETDATE()
IF @EndDate IS NULL SET @EndDate=DATEADD(DAY, -1, GETDATE())

SELECT @StartDate,@EndDate

END
GO
dbo.MYSP

edited Nov 16, 2018 at 20:28

Ilyes

15k5 gold badges31 silver badges61 bronze badges

answered Nov 16, 2018 at 20:28

Zeki Gumus

1,47410 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL - dynamic parameter in stored procedere [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related