Can someone explain how this works

• macbaze72

  SSC Enthusiast

  Points: 120

  More actions

  December 21, 2017 at 4:04 pm

  #330636

  This code works, but I don;t understand it.  The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate?  As it is written it return the results of dbo.startdate().

  ALTER PROCEDURE GetStartDate(
  @StartDate Datetime OUTPUT
  )
  AS
  DECLARE
      @Severity int

  SET @Severity = 0

  IF @StartDate IS NULL
      SET @StartDate = dbo.startdate(0)

  RETURN @Severity

  DECALRE @StartDate DateTime
  EXEC GetStartDate @StartDate OUTPUT
  SELECT @StartDate

  Thanks
  Everett

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  December 21, 2017 at 4:50 pm

  #1973227

  Post the DDL for dbo.startdate

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 21, 2017 at 5:09 pm

  #1973229

  macbaze72 - Thursday, December 21, 2017 4:04 PM

  This code works, but I don;t understand it.  The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate?  As it is written it return the results of dbo.startdate().

  ALTER PROCEDURE GetStartDate(
  @StartDate Datetime OUTPUT
  )
  AS
  DECLARE
      @Severity int

  SET @Severity = 0

  IF @StartDate IS NULL
      SET @StartDate = dbo.startdate(0)

  RETURN @Severity

  DECALRE @StartDate DateTime
  EXEC GetStartDate @StartDate OUTPUT
  SELECT @StartDate

  Thanks
  Everett

  The value generated by the procedure dbo.startdate(0) is returned in the OUTPUT parameter @StartDate.  The value in @Severity would be made available if GetStartDate is execute this way:
  declare @StartDate DateTime, @result int;
  EXEC @result = EXEC GetStartDate @StartDate OUTPUT;
  SELECT @result, @StartDate;

  Does that help?

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  December 21, 2017 at 5:15 pm

  #1973231

  macbaze72 - Thursday, December 21, 2017 4:04 PM

  This code works, but I don;t understand it.  The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate?  As it is written it return the results of dbo.startdate().

  ALTER PROCEDURE GetStartDate(
  @StartDate Datetime OUTPUT
  )
  AS
  DECLARE
      @Severity int

  SET @Severity = 0

  IF @StartDate IS NULL
      SET @StartDate = dbo.startdate(0)

  RETURN @Severity

  DECALRE @StartDate DateTime
  EXEC GetStartDate @StartDate OUTPUT
  SELECT @StartDate

  Thanks
  Everett

  Because @StartDate is the output parameter. In the beginning of the stored procedure it's declared as an output parameter and when executing the stored procedure, you are capturing the output parameter. Output parameters are explained in the documentation for Create Procedure - so it's not like it's easy to find.
  CREATE PROCEDURE (Transact-SQL)

  If you haven't worked with them much, you can find other posts and examples from other sites if you search on: output parameter in stored procedure

  Sue

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  December 22, 2017 at 7:15 am

  #1973259

  macbaze72 - Thursday, December 21, 2017 4:04 PM

  This code works, but I don;t understand it.  The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate?  As it is written it return the results of dbo.startdate(). ...
  Thanks
  Everett

  The RETURN value from any proc is always a single integer value.  This is intended to be the status of the proc, i.e., 0 if successful, something else if not.

  You don't need to, or want to, return OUTPUT parameters directly.  For one thing, you could have many OUTPUT parameters.  For another, SQL already "knows" it needs to return that value because of the "OUTPUT" designation.

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  December 22, 2017 at 9:26 am

  #1973275

  macbaze72 - Thursday, December 21, 2017 4:04 PM

  This code works, but I don;t understand it.  The part I need some clarification is on the Return @Severity. Why would that not be Return @StartDate?  As it is written it return the results of dbo.startdate().

  ALTER PROCEDURE GetStartDate(
  @StartDate Datetime OUTPUT
  )
  AS
  DECLARE
      @Severity int

  SET @Severity = 0

  IF @StartDate IS NULL
      SET @StartDate = dbo.startdate(0)

  RETURN @Severity

  DECALRE @StartDate DateTime
  EXEC GetStartDate @StartDate OUTPUT
  SELECT @StartDate

  Thanks
  Everett

  RETURN can only return integers and the RETURN in this code is basically useless someone considers a NULL to be a good return.  Considering the spelling error in the code, I suspect a lot is missing.  Please post the real code and post the code for the function that is called.

  I also suspect that this should be a function rather than a stored procedure.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)