In this post, we will focus on writing a simple stored procedure. We will additionally discuss using parameters to make our stored procedure more flexible and useable.

In a previous post we created a table named Accounting.BankAccounts and inserted two records. We also discussed transactions and errors. Please start there to set up the environment for this demonstration.

What is a Stored Procedure?

A stored procedure in SQL Server is a group of statements that can be saved and named in your database. The primary purpose is to be able to reuse more complex code without having to constantly re-type it.

Additionally, after the first time a stored procedure is executed, an execution plan is created and stored in the plan cache. Since the code in a stored procedure is not likely to change, the reuse of the execution plan will lead to improved performance of your queries. (Find out more: SQL Query Processing Explained for Beginners)

Starting Code and Ad-hoc Queries

Start with the code that was created in the writing transactions post. The code example will subtract $200 from account 1 and add $200 to account 2. This is an example of an ad-hoc query.

Ad-hoc queries are written and executed on the fly. They are great for one time query execution but are less likely to re-use execution plans. Plus, if there are dozens of lines of code, we may not have to re-type the code over and over again.

BEGIN TRY
	BEGIN TRANSACTION
		UPDATE Accounting.BankAccounts
		SET Balance -= 200
		WHERE AcctID = 1

		UPDATE Accounting.BankAccounts
		SET Balance += 200
		WHERE AcctID = 2
	COMMIT TRANSACTION
	PRINT 'Transaction Successful'
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	PRINT 'Divide by Zero Error. Error Number: ' + CAST(ERROR_Number() as char(4))
END CATCH
GO

Writing the Stored Procedure

To change the ad-hoc query we only need to add the following three lines to the beginning of the code. Only the CREATE PROCEDURE… AS and the name of the procedure is required. The OR ALTER will allow you to change the code if the procedure needs to be updated. We will use the names dbo.BankTransfer for this procedure.

CREATE OR ALTER PROCEDURE dbo.BankTransfer
AS

Executing the Stored Procedure

Once the stored procedure has been created, we can now execute it as often as we want with a single statement. Either the EXEC or EXECUTE statements could be used to run the procedure.

EXECUTE dbo.BankTransfer 

Using Parameters in a Stored Procedure

This stored procedure does exactly what it was intended to do. Subtract $200 from account 1 and add $200 to account 2. However, this does not provide us with any flexibility for other amounts or other accounts.

This is why we use parameters or arguments to allow others to input different values. This will make the procedure more flexible and performant. We start by defining the parameter values and their data types.

CREATE OR ALTER PROCEDURE dbo.BankTransfer
(@Amount money, @SubAccount int, @AddAccount int)
AS

For this example, we create an input parameter for the @Amount that needs to be transferred. We are using the money data type as that is the data type of the original table.

Two additional input parameters are created as int data types. The @SubAccount parameter will be used for the account we want to subtract the money from. The @AddAccount parameter will be used for the account we want to add the money to.

Now that we have defined the parameters, we need to update our transaction to replace the amounts and accounts with the appropriate information. Here is the complete procedure. I also added in a SET NOCOUNT ON statement, so we no longer see how many rows have been affected with the update.

CREATE OR ALTER PROCEDURE dbo.BankTransfer
(@Amount money, @SubAccount int, @AddAccount int)
AS
BEGIN TRY
SET NOCOUNT ON
	BEGIN TRANSACTION
		UPDATE Accounting.BankAccounts
		SET Balance -= @Amount
		WHERE AcctID = @SubAccount

		UPDATE Accounting.BankAccounts
		SET Balance += @Amount
		WHERE AcctID = @AddAccount
	COMMIT TRANSACTION
	PRINT 'Transaction Successful'
END TRY
BEGIN CATCH
	ROLLBACK TRANSACTION
	PRINT 'Divide by Zero Error. Error Number:' + CAST(ERROR_Number() as char(4))
END CATCH
GO

Now when we execute the stored procedure, we can use different amounts for different accounts.

EXECUTE dbo.BankTransfer 743.23, 2, 1