Return Code for an If Statement

• rjjh78

  SSCrazy

  Points: 2787

  More actions

  November 2, 2017 at 1:08 pm

  #329404

  Hello,  I am writing a Stored Procedure and would like to return an error code for a web application if a specific value passed is NULL.  I have attempted to do this with an IF STATEMENT but have since learned this is not possible.  What would be the correct way to accomplish this?  Below is my sample code.

  DECLARE @myvalue varchar(4)

     If @myvalue IS NULL
      BEGIN
      PRINT 'Stop Processing'
      RETURN -3
      END

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  November 2, 2017 at 1:19 pm

  #1966472

  Try the following. I set @myvalue to 6 as a test for a normal operation. Comment it out and you'll get a "-3" returned.
  Build your other logic around/in it. 

  
DECLARE @myvalue int
SET @myvalue = 6 

  begin
select ISNULL(@myvalue, -3)
end


  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  November 2, 2017 at 1:38 pm

  #1966475

  rjjh78 - Thursday, November 2, 2017 1:08 PM

  Hello,  I am writing a Stored Procedure and would like to return an error code for a web application if a specific value passed is NULL.  I have attempted to do this with an IF STATEMENT but have since learned this is not possible.  What would be the correct way to accomplish this?  Below is my sample code.

  DECLARE @myvalue varchar(4)

     If @myvalue IS NULL
      BEGIN
      PRINT 'Stop Processing'
      RETURN -3
      END

  Why do you say it's not possible? Here's an example from Microsoft themselves (link)

  USE AdventureWorks2012;
GO

  CREATE PROCEDURE checkstate @param VARCHAR(11)
AS
IF
(
  SELECT StateProvince
  FROM Person.vAdditionalContactInfo
  WHERE ContactID = @param
) = 'WA'
  RETURN 1;
ELSE
  RETURN 2;
GO

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  November 2, 2017 at 6:42 pm

  #1966515

  Henrico Bekker - Thursday, November 2, 2017 1:19 PM

  Try the following. I set @myvalue to 6 as a test for a normal operation. Comment it out and you'll get a "-3" returned.
  Build your other logic around/in it. 

  
DECLARE @myvalue int
SET @myvalue = 6 

  begin
select ISNULL(@myvalue, -3)
end


  That doesn't provide the conditional early exit though.

  --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)
  

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  November 2, 2017 at 6:45 pm

  #1966516

  rjjh78 - Thursday, November 2, 2017 1:08 PM

  Hello,  I am writing a Stored Procedure and would like to return an error code for a web application if a specific value passed is NULL.  I have attempted to do this with an IF STATEMENT but have since learned this is not possible.  What would be the correct way to accomplish this?  Below is my sample code.

  DECLARE @myvalue varchar(4)

     If @myvalue IS NULL
      BEGIN
      PRINT 'Stop Processing'
      RETURN -3
      END

  It works just fine on my machine.  Understand that you can't include the RETURN value when running a script from SSMS.  It will, however, work in a stored procedure with the -3.

  --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)