Stored Procedures

• What is a stored procedure?

  Routines that

  • Accept input parameters
  • Perform actinos (EXECUTE, SELECT, INSERT, UPDATE, DELETE, and other SP statments)
  • Return status (success or failure)
  • Return output parameters

• Why use stored procedures?

  • Can reduce execution time
  • Can reduce network traffic
  • Allow for Modular Programming
  • Improved Security

• What’s the difference?

  UDFs	SPs
  Must return value - table value allowed	Return value optional - No table valued
  Embeded SELECT execute allowed	Cannot embed in SELECT to execute
  No output parameters	Return output parameters & status
  No INSERT, UPDATE, DELETE	INSERT, UPDATE, DELETE allowed
  Cannot execute SPs	Can execute functions & SPs
  No Error Handing	Error Handing with TRY....CATCH

• Create Procedure with Output parameter

        -- First four lines of code
        -- SP name must be unique
        CREATE PROCEDURE dbo.cuspGetRideHrsOneDay
            @DateParm date,
            @RideHrsOut numeric OUTPUT
        AS
        SET NOCOUNT ON
        BEGIN
        SELECT
            @RideHrsOut = SUM(
            DATEDIFF(second, PickupDate, DropoffDate)
            )/ 3600
        FROM YellowTripData
        WHERE CONVERT(date, PickupDate) = @DateParm
        RETURN
        END;
  

• Output parameters vs return values

  Output Parameters	Return Value
  Can be any data type	Used to indicate success or failure
  Can declare multiple per SP	Integer data type only
  Cannot be table-valued parameters	0 indicates success and non-zero indicates failure

---

CRUD

• Why stored procedures for CRUD?

  • Decouples SQL code from other application layers
  • Improved Security
  • Performance

• C for CREATE

        CREATE PROCEDURE dbo.cusp_TripSummaryCreate(
            @TripDate as date,
            @TripHours as numeric(18,0)
        ) AS BEGIN INSERT INTO dbo.TripSummary(Date, TripHours)
        VALUES
            (@TripDate, @TripHours)
        SELECT Date, TripHours
        FROM dbo.TripSummary
        WHERE Date = @TripDate
        END
  

• R for READ

        CREATE PROCEDURE cusp_TripSumaryRead
            (@TripDate as date)
        AS 
        BEGIN
        SELECT Date, TripHours
        FROM TripSummary
        WHERE Date = @TripDate
        END;
  

• U for UPDATE

        CREATE PROCEDURE dbo.cusp_TripSummaryUpdate
            (
                @TripDate as date,
                @TripHours as numeric(18,0)
            )
        AS 
        BEGIN
        UPDATE dbo.TripSummary
        SET DATE = @TripDate,
            TripHours = @TripHours
        WHERE DATE = @TripDate
        END;
  

• D for DELETE

        CREATE PROCEDURE cusp_TripSummaryDelete
            (@TripDate as date,
            @RowCountOut int OUTPUT)
        AS
        BEGIN
        DELETE
        FROM TripSummary
        WHERE Date = @TripDate
  
        SET @RowCountOut = @@ROWCOUNT
        END;
  

---

Let’s EXEC!

• Ways to EXECute

  • No output parameter or return value
  • Store return value
  • With output parameter
  • With output parameter & store return value
  • Store result set

  Example:

        DECLARE @RideHrs as numeric (18,0)
  
        EXEC dbo.cuspSumRideHrsOneDay
            @DateParm = '1/5/2017',
            @RideHrsOut = @RideHrs OUTPUT
  
  
        SELECT @RideHrs  as TotalRideHrs
  

NEED TO ADD MORE….