Default Dates for Global Variable

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  April 18, 2018 at 12:10 pm

  #344788

  Hello,  I am looking to have my PROC select a default date range if the values passed are NULL.  If the values for StartDt and/or EndDt are NULL, I would like the StartDt to equal the first day of the year 1/1/YYYY and the EndDate equal to the current Date.  My code is able to do as desired when I set the variables, but this is also overriding the passed parameters as well. 

  How can I make it so that the global variables are only SET if the values being passed are NULL?My code is as follows and thanks in advance!
  

  CREATE Procedure [dbo].[getDashboard]

    @NTLOGON varchar(100),
  @StartDt DATETIME = NULL,
  @EndDt  DATETIME = NULL

  As
SET NOCOUNT ON

  SET @StartDt = (select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)))
SET @EndDt = getdate()

  SELECT * FROM ss_Test
  WHERE User = @user-id AND Create_Dt BETWEEN @StartDt AND @EndDt

• DesNorton

  SSC-Insane

  Points: 24283

  More actions

  April 18, 2018 at 1:14 pm

  #1987418

  rjjh78 - Wednesday, April 18, 2018 12:10 PM

  Hello,  I am looking to have my PROC select a default date range if the values passed are NULL.  If the values for StartDt and/or EndDt are NULL, I would like the StartDt to equal the first day of the year 1/1/YYYY and the EndDate equal to the current Date.  My code is able to do as desired when I set the variables, but this is also overriding the passed parameters as well. 

  How can I make it so that the global variables are only SET if the values being passed are NULL?My code is as follows and thanks in advance!
  

  CREATE Procedure [dbo].[getDashboard]

    @NTLOGON varchar(100),
  @StartDt DATETIME = NULL,
  @EndDt  DATETIME = NULL

  As
SET NOCOUNT ON

  SET @StartDt = (select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)))
SET @EndDt = getdate()

  SELECT * FROM ss_Test
  WHERE User = @user-id AND Create_Dt BETWEEN @StartDt AND @EndDt

  Use IF statements to determine whether the variables are NULL.
  IF (@StartDt IS NULL)
BEGIN
--SET @StartDt = (select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)));
SET @StartDt = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0);
END;

  IF (@EndDt IS NULL)
BEGIN
SET @EndDt = GETDATE();
END;


  ---

  How to post data/code on a forum to get the best help.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  April 18, 2018 at 5:34 pm

  #1987435

  CREATE Procedure [dbo].[getDashboard]

    @NTLOGON  varchar(100),
    @StartDt  DATETIME = NULL,
    @EndDt  DATETIME = NULL

  As
  SET NOCOUNT ON

  SELECT @StartDt = IsNull(@StartDt, DateAdd(yy,DateDiff(yy,0,GetDate()),0))
       , @EndDt = IsNull(@EndDt, GetDate());

  SELECT * FROM ss_Test
    WHERE User = @user-id AND Create_Dt BETWEEN @StartDt AND @EndDt;

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  April 20, 2018 at 6:14 am

  #1987644

  As long as we're at it, we might as well correct what appears to be a slight typo, and perhaps simplify some of the code:CREATE PROCEDURE dbo.getDashboard (
@NTLOGON varchar(100),
@StartDt datetime = NULL,
@EndDt datetime = NULL
)
AS
SET NOCOUNT ON;

  SELECT    @StartDt    = ISNULL(@StartDt, DATEFROMPARTS(YEAR(GETDATE()), 1, 1)),
        @EndDt        = ISNULL(@EndDt, GETDATE());

  SELECT *
FROM ss_Test
WHERE [User] = @NTLOGON
    AND Create_Dt BETWEEN @StartDt AND @EndDt;
GO

  The parameter @NTLOGON should probably be where @user-id is...   I also used DATEFROMPARTS because it's easier to read and understand..   Finally, I standardized the capitalization, so that ALL of the SQL keywords are in CAPS, and all data types are lowercase, and then just added a GO at the end, and some white space to make the SELECT easier to read.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)