Stored Procedures

BrandonGalderisi — Fri, 06 Mar 2009 23:54:00 GMT

Stored procedures are part of the core functionality of a DBMS. SQL Server is no exception to that. Stored procedures have many uses. They can be used to handle basic CRUD operations as well as complex logic. There are several things that you have to consider when it comes to building stored procedures for you database.

Is this something that has to be done in the database?
Is this something that should be done in the database?
Does my database server have the capacity to handle this as it's use scales.

Above there are three questions you need to ask yourself but they are not the only three. Since there is no way for us to answer any of these questions, we'll talk briefly about it and concentrate on the what you can do and how.

Is this something that has to be done in the database?
There are benefits to keeping your logic contained in the database. It's easy to patch by installing new procedures or updating existing ones. All of the logic is contained inside of one location instead of being spread out in embedded SQL in applications.

Is this something that should be done in the database?
SQL Server's primary purpose is to store your information. And it's great at a lot of things, and not at others. The more work that is done in the database, the higher the load placed on the database server. This can lead to a lot of issues including increased CPU and memory usage which can take away from SQL Server's ability to do what it HAS to do.

Does my database server have the capacity to handle this as it's use scales?
I can't tell you how many times I've heard people say "there will only be 10 users" or "there will never be more than a few hundred records". If you write inefficient SQL because you expect small usage, then by habit you will write inefficient SQL for large scale operations. I'm not saying to over-engineer every table. Sometimes it is OK to bend the rules a little. But it has to be a clear and conscious decision to do such

Now on to Stored Procedures. I will first start by showing a very simple stored procedure that shows the structure, then we'll discuss it's structure. There are are lot of different things to know, even about the basics of stored procedures. So this will be a rather long posting.

CREATE PROCEDURE dbo.up_SelectHello
as
set nocount on
select 'Hello'
return
go

Above is a very basic, and impractical, procedure that does nothing other than select the word 'Hello'. To create a stored procedure, you start off by saying "CREATE PROCEDURE". The next thing that you do is give the name. You do not have to specify the schema, but it's a good idea to do so. Above, I have selected the dbo schema. The next thing after the name is the keyword "as". As has many uses in SQL Server. In stored procedures, it is used to separate the definition of the stored procedure from the code of the stored procedure. The next line, "set nocount on" is not specific to stored procedures, but it will prevent the stored procedure from outputing the number of rows effected by various operations in the query. Next is the simple select statement followed by return. Return is not required in stored procedures. But when a return is executed, the procedure exits immediately.

EXEC dbo.up_SelectHello

Calling the procedure from within SQL Server Management Studio is as simple as running the above command in a query window. An important thing to note is that if you are only executing one command in a batch, you do not need to include the EXEC. So the below statements are all valid was to call the procedure.

EXEC dbo.up_SelectHello

dbo.up_SelectHello

dbo.up_SelectHello
go
select 'Something Else'

This last is valid because the GO separates the first and second statement into two different batches.

CREATE PROCEDURE dbo.up_SelectInput
  @Input nvarchar(100)
as
set nocount on
select @input
return
go

The above goes one step further and uses an input parameter as the text to select. While this procedure is equally useless, it shows the structure of allowing input parameters. The structure is that you include the variable name and it's data type between the procedure name and the "as" statement. You can also provide multiple parameters by separating them by commas, as shown below.

CREATE PROCEDURE dbo.up_SelectInput
  @Input   nvarchar(100)
 ,@Input2  nvarchar(100)
as
set nocount on
select @input + @input2
return
go

Going to the next level, you can provide default values for input parameters.

CREATE PROCEDURE dbo.up_SelectInput
  @Input nvarchar(100) = N'Hello'
as
set nocount on
select @input
return
go

What this does is allow the parameter to have a value if the parameter is not specified in the execution call. This does not mean that if you specify NULL that it will use the value So if the following 3 procedure calls are made:

EXEC dbo.up_SelectInput @input=N'Hello'
EXEC dbo.up_SelectInput 
EXEC dbo.up_SelectInput NULL

The results in order will be:

Note that for the last call, I did not specify @input in the EXEC statement. SQL Server does not require you to specify the parameter name when passing them to a procedure call. Instead, it will accept the parameters in the order in which they are provided. But if you specify the name for a parameter, you must specify the name for all subsequent parameters.

So with the following procedure:

CREATE PROCEDURE dbo.up_SelectInput
  @Input   nvarchar(100) = N'hello'
 ,@Input2  nvarchar(100) = N'goodbye'
as
set nocount on
select @input + N' ' + @input2
return
go

You will get the following results with these commands:

exec dbo.up_SelectInput @input=N'SQL Server' ,@input2=N'Nation'

exec dbo.up_SelectInput N'SQL Server',@input2=N'Nation'

exec dbo.up_SelectInput @input=N'SQL Server', N'nation'

Msg 119, Level 15, State 1, Line 1
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Output parameters allow you to get data OUT of a stored procedure. In order to specify a parameter as an OUTPUT parameter, you add the keyword OUTPUT after the data type definition. This does not mean that you can not pass a value in if it is defined as output, but it allows you to pass a value out. In the below procedure, you will see that we are concatenating the first two parameters and assigning it to the third parameter.

CREATE PROCEDURE dbo.up_SelectInput
  @param1  nvarchar(100) = N'hello'
 ,@param2  nvarchar(100) = N'goodbye'
 ,@param3  nvarchar(100) output
as
set nocount on
set @param3 = @param1 + N' ' + @param2  
return
go

Now to get the value from the parameter, it is a little different. Since executing the procedure will not select any data, we have to retrieve the value using a parameter. Below we will pass in the parameter @output to accept the value of @param3.

declare @output nvarchar(100)
exec dbo.up_SelectInput @param1 = N'SQL Server', @param2 = 'Nation', @param3 = @output output
select @output

Selecting @output you will see the the following result.

The last thing that I want to cover in this is the difference between return codes and output parameters. The return code is the status of the procedure. It is the value that is returned by the RETURN statement in the procedure. By default, if you do not specify a value in the RETURN statement, the default of 0 is used. You can only return an INTEGER from a procedure. You can not return dates, or any other data types. Only integers. The default return code of 0 typically indicates that that the procedure executed successfully.

Let's test using return codes. We will start with the same basic procedure that we have been using. and we will show how to call the procedure to capture the return code. Unlike output parameters, return codes are captured using the "exec @parameter = procedure" syntax.

CREATE PROCEDURE dbo.up_SelectInput
  @param1  nvarchar(100) = N'hello'
 ,@param2  nvarchar(100) = N'goodbye'
 ,@param3  nvarchar(100) output
as
set nocount on
set @param3 = @param1 + N' ' + @param2  
return 
go
declare @output          nvarchar(100)
       ,@ReturnCode      INT
exec @ReturnCode = dbo.up_SelectInput @param1 = N'SQL Server', @param2 = 'Nation', @param3 = @output output

select @ReturnCode

As you will see, the value of @returncode is 0.

CREATE PROCEDURE dbo.up_SelectInput
  @param1  nvarchar(100) = N'hello'
 ,@param2  nvarchar(100) = N'goodbye'
 ,@param3  nvarchar(100) output
as
set nocount on
set @param3 = @param1 + N' ' + @param2  
return 1
go

declare @output          nvarchar(100)
       ,@ReturnCode      INT
exec @ReturnCode = dbo.up_SelectInput @param1 = N'SQL Server', @param2 = 'Nation', @param3 = @output output

select @ReturnCode

As you will see, the value of @returncode is 1.

Return codes are useful for passing information back about what happened in a procedure. For example a user authentication procedure could be used to validate that a username and password hash passed in are valid for a particular user. Using return codes you could differentiate between a successful authentication, a failed password match, an invalid username, or anything else you would desire.

I hope that you enjoyed this overview on the basic structure of stored procedures. Understanding how to construct stored procedures is an essential skill that you will need if you want to become a SQL Server master.

How Tos : return, beginner

Stored Procedures