Logic in stored procedure

Question

I have need of some logic in a stored procedure. All the stored procedure does it perform a couple of logic rules and then returns a true or false depending on the result.

The pseudo SQL code:

CREATE TABLE #PV ([Date] DATETIME, Dis FLOAT, Del Float, Sold Float)
INSERT #PV exec GetPVSummaryReport @ID, @PID, @From, @To
SELECT AVG(Dis) / 8 AS DisAvg, AVG(Del) AS DelAvg FROM #PV
IF DisAvg > 20 -- this is the bit I am having problems grokking
    RETURN TRUE
ELSE
    -- do longer calculation

How do you do this sort of logic?

Notes about the code: The table #PV has 4 fields - those provided (Date, Dis, Del and Sold).

Answer 1

declare @DisAvg float
declare @DelAvg float

-- Instantiate #PV etc

select
  @DisAvg = avg(Dis) / 8,
  @DelAvg = avg(Del)
from
  #PV

if @DisAvg > 20
  return
else
  -- Do something else

Answer 2

You need to declare a variable and select into it

DECLARE @DisAvg DOUBLE, @DelAvg DOUBLE
SELECT @DisAvg = AVG(Dis) / 8, @DelAvg = AVG(Del) #PV
IF @DisAvg > 20 -- this is the bit I am having problems grokking
 RETURN 1
ELSE
 -- do longer calculation

Declare with the correct type of the Dis field.

Edit - corrected the return value.

Answer 3

DECLARE @DisAvg DECIMAL
DECLARE @ReturnValue bit

// use SET or SELECT to assign values to @DisAvg

IF (@DisAvg > 20)
BEGIN 
    SET  @ReturnValue = 1
END
ELSE
BEGIN

    -- do longer calculation
SET @ReturnValue = 0
END 
SELECT @ReturnValue

Only integers can be returned from the RETURN statement, you can use SELECT instead

Answer 4

Guys,

why is everyone creating a Temp Table? Variable tables are so much nicer (and more performant) :)

lets see...

DECLARE @PV TABLE ([Date] DATETIME,
    Dis FLOAT,
    Del FLOAT,
    Sold FLOAT)

INSERT INTO @PV
EXEC [dbo].[GetPVSummaryReport] @ID, @PID, @From, @To


-- Create some variables, which will contain the results.
DECLARE @DisAvg AS FLOAT,
    @DelAvg AS FLOAT

-- Retrieve results into a variables.
SELECT @DisAvg = AVG(Dis) / 8, @DelAvg AVG(Del)
FROM @PV

-- Check results...    
IF @DisAvg > 20 
   RETURN TRUE
ELSE BEGIN
    -- do longer calculation
END

-- NOTE: I'm not sure where you use @DelAvg .. 
--       I'm assuming it's in the 'do longer calculation' section.

Good luck!

Answer 5

LABELS are a beautiful things. LABELS allow you to use GOTO's and this lets you write simple to follow logic inside your stored procedures.

DECLARE @Result int
SET @Result = 1 -- 1=True 0=False

IF TEST1 Failed
BEGIN
  SET @Result = 0
  GOTO ENDPROCESSING
END

IF TEST2 Failed
BEGIN
  SET @Result = 0
  GOTO ENDPROCESSING
END
IF TEST3 Failed
BEGIN
  SET @Result = 0
  GOTO ENDPROCESSING
END

ENDPROCESSING:
SELECT @Result