SQL Server stored procedures are only as powerful as the techniques that you use to create them. This tutorial introduces you to some advanced concepts such as output and optional parameters (hacks), transaction processing for data integrity and how to handle errors using try and catch blocks when something goes wrong inside your stored procedure. This tutorial covers the following topics
  • SQL Server stored procedure output parameters
  • SQL server stored procedure optional parameters
  • SQL Server stored procedure transaction processing
  • SQL Server stored procedure error handling using try and catch blocks
  • Grant permissions on stored procedure
If you haven’t read the first tutorial Complete Beginner’s guide to SQL Server Stored Procedures with real world examples then I strongly you read it and download the sample real world example database included. You will need it for this tutorial.

Case study scenario

Our database is for a simple customer orders database. Let’s assume the client wants us to include invoicing support to the database. In addition to that, a third party program will be used for processing payments. The third party application needs access to your database directly. You decided to use stored procedures for security reasons and limit what the third party can do and see. In a nutshell, the third party application will need to;
  • Verify if a customer id exists or not (output parameters)
  • Lookup customers using either customer id or customer name or list all the customers (instead of creating separate stored procedures, you can use a hack that mimics optional parameters and just have a single procedure)
Your database will need to;
  • Maintain data integrity – in a nutshell, if your updates affect more than one table, then all tables should be update or all tables should not be updated if an error occurs (transaction processing and error handling)
  • Protect data confidentiality goal of information and access control – not all information in the database needs to be exposed to the third party. All interactions should be using stored procedures and the actions should be limited to what the third party has been authorized to do. (Setting permissions on SQL stored procedures)
Now that we have laid the foundation firmly, let’s get started. Remember prefixes consistency are important when naming stored procedures. All our stored procedures for the third party application will start with upExternalPayments{actionName}

SQL Server stored procedure output parameters

As the name suggests, output parameters are parameters that you define in the stored procedure that can be used in the statement that returns the results of the stored procedure. The following code creates a stored procedure that verifies the validity of a customer id. It returns true if valid and false if it doesn’t exist. [sql] CREATE PROCEDURE [dbo].[ upExternalPaymentsVerifyCustomerId] @customer_id int, @valid_id bit OUTPUT AS BEGIN SELECT @valid_id = COUNT(customer_id) FROM customers WHERE customer_id = @customer_id; END [/sql] HERE,
  • “@valid_id bit OUTPUT” will be used in the result set returned by the stored procedure.
Calling our stored procedure [sql] DECLARE              @valid_id bit EXEC      [dbo].[ upExternalPaymentsVerifyCustomerId] @customer_id = 1 , @valid_id = @valid_id OUTPUT SELECT  @valid_id as N'@valid_id' [/sql] HERE,
  •  “DECLARE @valid_id bit” declares a variable that will be used to receive the output from the OUTPUT parameter in the stored procedure. Note: It has to be passed as a parameter and assigned the value of the OUTPUT parameter in the stored procedure.
Executing the above query gives the following results advance_sp_output_parameter

SQL server stored procedure optional parameters

SQL stored procedures do not necessary support optional parameters but there is a hack around it. Suppose you want the third party payments application to be able to search for customers using the customer id and/or name or display all customers from the database. Creating separate stored procedures for all combinations can be a solution but a better one exists. The following code creates the customers lookup stored procedure [sql] CREATE PROCEDURE [dbo].[upCustomersLookUp] @customer_id int, @name varchar(145) AS BEGIN SET NOCOUNT ON; SELECT customer_id, name, email, contact_person FROM customers WHERE customer_id = CASE WHEN @customer_id IS NULL THEN customer_id ELSE @customer_id END AND name LIKE CASE WHEN @name IS NULL THEN name ELSE '%' + @name + '%' END END [/sql] HERE,
  • “CASE WHEN @variable IS NULL THEN field_name ELSE @variable END” the case statement checks the content of the parameter. If it is NULL then it’s literally ignored using field_name = field_name which will always be true. If it is not null then it is considered in the statement using field_name = value.
Displaying all the customers [sql] EXEC [dbo].[upCustomersLookUp] @customer_id = NULL, @name = NULL [/sql] Store procedure results advance_sp_oprtinal_parameters_allDisplay a customer record using id [sql] EXEC [dbo].[upCustomersLookUp] @customer_id = 1, @name = NULL [/sql] Stored procedure results advance_sp_oprtinal_parameters_idDisplay customer record(s) using name Note: the name uses wildcards to perform searches [sql] EXEC [dbo].[upCustomersLookUp] @customer_id = NULL, @name = 'ac' [/sql] Stored procedure results advance_sp_oprtinal_parameters_name

SQL server stored procedure transaction try catch

Using our orders database as an example, let’s assume the client has asked us to include a table for generating customer invoices. The invoice should be generated when an order has been delivered. The invoice table is related to the orders table using the order id. The script below is for the invoices table [sql] CREATE TABLE [dbo].[invoices]( [invoice_id] [int] IDENTITY(1,1) NOT NULL, [order_id] [int] NOT NULL, [invoice_date] [datetime] NULL, [amount] [money] NULL, [date_created] [datetime] NULL, [date_updated] [datetime] NULL, [created_from_ip] [varchar](45) NULL, [updated_from_ip] [varchar](45) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[invoices] ADD  CONSTRAINT [DF_invoices_amount]  DEFAULT ((0)) FOR [amount] GO ALTER TABLE [dbo].[invoices] ADD  CONSTRAINT [DF_invoices_date_created]  DEFAULT (getdate()) FOR [date_created] GO ALTER TABLE [dbo].[invoices] ADD  CONSTRAINT [DF_invoices_date_updated]  DEFAULT (getdate()) FOR [date_updated] GO ALTER TABLE [dbo].[invoices] ADD  CONSTRAINT [DF_invoices_created_from_ip]  DEFAULT (NULL) FOR [created_from_ip] GO ALTER TABLE [dbo].[invoices] ADD  CONSTRAINT [DF_invoices_updated_from_ip]  DEFAULT (NULL) FOR [updated_from_ip] GO ALTER TABLE [dbo].[invoices]  WITH CHECK ADD  CONSTRAINT [FK_invoices_orders] FOREIGN KEY([order_id]) REFERENCES [dbo].[orders] ([order_id]) GO ALTER TABLE [dbo].[invoices] CHECK CONSTRAINT [FK_invoices_orders] GO [/sql] The stored procedure called when an order has been delivered will only accept the order id and use it to update the orders table and generate an invoice using the order id. The stored procedure will have two statements;
  • Update delivered field in orders table to true
  • Insert a new record into invoices table
In order to maintain data integrity, if any of the queries fail, the changes made by the other query should be rolled back. The following code creates the stored procedure that does that. [sql] CREATE PROCEDURE [dbo].[upProductsOrderDeliveredCreateInvoice] @order_id int AS BEGIN TRANSACTION; BEGIN TRY UPDATE orders SET delivered = 1 WHERE order_id = @order_id; INSERT INTO invoices (order_id,invoice_date,amount) VALUES (@order_id,GETDATE(),2500); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION; [/sql] HERE,
  • “BEGIN TRANSACTION” initiates transaction processing
  • “BEGIN TRY… END TRY BEGIN, BEGIN CATCH… END CATCH” defines the error handling block. If an exception occurs, then it is handled in the BEGIN CATCH… END CATCH block.
  • “IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;” @@TRANCOUNT is a system variable that keeps count of open transactions. We have included it to avoid executing the rollback command when we do not have any open transactions. @@TRANCOUNT is incremented by one (1) when a transaction is opened and decremented by 1 when ROLLBACK TRANSACTION is executed.
  • “ERROR_keyword” is used to access detailed error information. Returning the error information as shown above helps developers know when went wrong when troubleshooting.
Note: we have hard coded the invoice amount for simplicity’s sake. In a real application, we would compute the invoice amount based on the quantities in order details table (filtered using the order id) and product prices from products table. Let’s suppose an invalid order id has been passed as a parameter. This would violate the foreign key constraint in the invoice table. The following code executes our upProductsOrderDeliveredCreateInvoice stored procedure. [sql] EXEC      [dbo].[upProductsOrderDeliveredCreateInvoice] @order_id = 5 [/sql] Since our database does not have any order with id 5, our stored procedure will return the following value. advance_sp_transaction_processing

Grant permissions on stored procedure

This section assumes you have already created a user in SQL server (external payments) with read only access to the orders database. (ask the comments section if you need help in creating the users in SQL and how to map them to the database) Right click on the stored procedure and select properties stored_procedure_set_permissionSelect the permissions tab stored_procedure_set_permission_tab Enter the name of the user id that you want to grant access permission stored_procedure_set_permission_select_user Select the user then click on OK button Grant execute stored procedure right only as shown below stored_procedure_set_permission_rights Click on OK button Repeat the above process for upExternalPaymentsVerifyCustomerId Login into SQL server management studio using external_payments user id You should be able to see the following two stored procedures only locked stored_procedure_set_permission_execute_only

Summary

As you can see from the above examples, SQL stored procedures if used properly and when it’s appropriate to use them can turn you into a black belt database design/developer. You can do so much more with optional parameters, maintain data integrity with transaction processing, sweeten the developer’s lives when debugging with error messages display, and restrict access to the database and what other third party developers can see and do with your database.