Stored procedures

Writing Functions and Stored Procedures in SQL Server

Meghan Kwartler

IT Consultant

What is a stored procedure?

What?

Routines that

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

Why use stored procedures?

Why?

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

What's the difference?

UDFs

Must return value
- Table-valued allowed
Embedded SELECT execute allowed
No output parameters
No INSERT, UPDATE, DELETE
Cannot execute SPs
No Error Handling

SPs

Return value optional
- No table-valued
Cannot embed in SELECT to execute
Return output parameters & status
INSERT, UPDATE, DELETE allowed
Can execute functions & SPs
Error Handling 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
.......

CREATE PROCEDURE with OUTPUT parameter

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

Can be any data type
Can declare multiple per SP
Cannot be table-valued parameters

Return value

Used to indicate success or failure
Integer data type only
0 indicates success and non zero indicates failure

You're ready to CREATE PROCEDUREs!

Writing Functions and Stored Procedures in SQL Server