1. Stored procedures
It's time to learn about stored procedures, another great tool to add to your SQL skills.
2. What is a stored procedure?
What is a stored procedure, or SP? It's a routine that can accept input parameters, perform an action, and return success status and output parameters. Some of these characteristics should sound familiar; maybe like the user defined functions we discussed earlier? They are similar, but not the same. Within stored procedures you can EXECUTE INSERT, UPDATE, SELECT, DELETE and call other stored procedures as well. These statements can change data outside of the SP scope.
3. Why use stored procedures?
Why should we use stored procedures? They can reduce execution time, network traffic, and allow for modular programming like user defined functions, but they can also improve database security. Stored procedures can be written and utilized to help prevent SQL injection attacks which are a nefarious technique where SQL statements are injected into a database.
4. What's the difference?
Let's review the differences between UDFs and Stored Procedures. The only characteristics they actually share are accepting input parameters and performing an action. The rest are distinct differences. SPs don't have to return a value and technically can't return a table valued result. They cannot be executed in a simple SELECT statement but can return output parameters, return status, and result sets. You can execute functions and SPs from within an SP, as well as INSERT, UPDATE and DELETE statements. You can also do effective error handling in stored procedures.
5. CREATE PROCEDURE with OUTPUT parameter
Let's look at the first four lines of SQL Server syntax to create a stored procedure. Here we are using the logic from the GetRideHrsOneDay() function discussed previously.
We use the CREATE PROCEDURE keyword followed by the schema and stored procedure name. Stored procedure names must be unique across the schema, including UDFs. You can't have the same name as a user defined function so we added a cusp prefix here.
Following the SP name is the input parameter @DateParm and its date data type. An output parameter, @RideHrsOut, is defined as a numeric data type and the OUTPUT keyword indicates it should be returned as output. Parameters still need to be named with the at symbol as the first character but are not required to be enclosed by parenthesis, like in UDFs.
6. CREATE PROCEDURE with OUTPUT parameter
The SET NOCOUNT ON statement on the 4th line prevents SQL from returning the number of rows affected by the stored procedure to the caller. This is optional, and some consider it best practice, but it can cause issues for the calling application if it's expecting this data to be returned.
The rest of the statement is the same as the GetRideHrsOneDay() UDF created previously, except we are assigning the result of the SELECT statement to the output parameter @RideHrsOut. The RETURN keyword is optional and instructs the SP to return the output parameter to the calling application immediately.
7. Output parameters vs. return values
What are output parameters and return values? Output parameters are defined similar to input parameters, but with the OUTPUT keyword. They can be any data type except table valued. You can also declare multiple output parameters per stored procedure.
Return values in SPs are generally used to indicate success or failure to the calling application. SPs can only return integer data types and zero indicates success. Non-zero values indicate that the SP experienced some type of error during execution.
8. You're ready to CREATE PROCEDUREs!
It's your turn to create your own stored procedures. Remember, you are only creating these procedures, so there won't be query results in the exercises. You will learn how to execute them later in the chapter. Have fun!