SQL Server (2008): First Stored Procedure

Overview

MS SQL Servers always support stored procedures. For general introduction to stored procedure (regardless of database engine) please visit this wikipedia page. Visit this msdn page for detailed (official) overview of stored procedures in MS SQL Server.

Benefits of Stored Procedures

Less network traffics
With stored procedure, you can execute a lot of commands with just a single call. This of course will reduce network traffic, due to reducing a huge number of calls into just a single call. In the end, this would result in the increase of response speed or throughput of the system.
Less recompilation of T-SQL commands
Prior actual execution, each T-SQL command issued to the server actually must be compiled to get best strategy for the execution. Simple example of this compilation is the process of determining which indexes to be used and in what order. This strategy is called execution plan. The calculation of execution plan requires time that of course will slow down the overall execution time. With stored procedures, however, this compilation only takes one time (when the server starts) or when the stored procedure changed. This would reduce time greatly, especially for stored procedure containing a lot of commands.
Allow very fine definition of access right.
Through store procedure you might allow access to certain database items that normally not available to the user executing the stored procedure. This allow you to hide sensitive information while still be able to alter it "behind the scene".
Business logic encapsulation

Disadvantage of Stored Procedures

Normally there is no disadvantage of stored procedure. However poorly written stored procedures may introduce some problems, like forgetting to commit/rollback transaction, forget to unlock tables, infinite loop, etc.

Our First Stored Procedure, Hello World

Create our First Stored Procedure

Now let's start with our new stored procedure. Let's not be too ambitious with our journey. Let's make a stored procedure that says "Hello World" anytime it executes. Enter the famous HelloWorld!

Open your SQL Server Management Studio (or your favorite SQL Server management software), connect to your test database, open new query window, and paste the following T-SQL codes.

CREATE PROCEDURE dbo.HelloWorld
AS
BEGIN
  SELECT 'Hello World'
END

Then execute the T-SQL. Everything should go well, and you will get new stored procedure named HelloWorld, like shown below.

How to Use the Stored Procedure?

It is nice that now we have the power of HelloWorld. Don't you now feel the world a little bit safer? If so, you are a weird kid.. lol. Just kidding. Such great power however means nothing if you don't know how to use it. So, how do we use a stored procedure?

We use a stored procedure by execute it. To execute a stored procedure you just call EXEC or EXECUTE command followed by the stored procedure name. To execute our HelloWorld, we use the following command.

EXEC HelloWorld

EXECUTE HelloWorld

When you issue this command, you would get something like this. Notice the red-circled "Hello World"? That is the result of invocation of HelloWorld.

Providing Input to Stored Procedures

Just like routines in other programming languages, many stored procedures require initial information to be provided for them to work properly. For them we need to provide input data upon execution. For this we need the help of parameter, input parameter to be specific.

To create an input parameter, you need to specify its name, data type, and optional default value in the stored procedure header area (the area between name and "AS" keyword). Each parameter should be separated by comma. Parameter names must always start with @.

The following example shows a stored procedure that has 3 input parameters, @Param1 of int data type, @Param2 of nvarchar(50) data type, and @Param3 of tinyint data type with default value of 127.

CREATE PROCEDURE TestInputParams
  @Param1 int
  , @Param2 nvarchar(50)
  , @Param3 tinyint=127
AS
....

Let's create a simple echoing stored procedure that echo combination of its input parameters given to it. We'll name it Echo and we declare it like this.

CREATE PROCEDURE Echo
  @Text1 nvarchar(254)
  , @Text2 nvarchar(50)
  , @Sender nvarchar(50)='System'
AS
BEGIN
  SELECT @Text1 + '; ' + @Text2 + ' (from: ' + @Sender + ')'
END

There are two ways to execute Echo. First is to execute it by supplying both parameter names and values, like shown below.

EXECUTE Echo @Text1='Hello World', @Text2='Have a nice day!', @Sender='MySelf'

With this approach you don't have to provide the values in any kind of order. For example, the following call would be valid and gives the same result with the previous one.

EXECUTE Echo @Sender='MySelf', @Text1='Hello World', @Text2='Have a nice day!'

The second way would be to omit parameter names, but this way force you to provide the parameters in the exact order they are declared. For example the following call is okay.

EXECUTE Echo 'Hello World', 'Have a nice day', 'MySelf'

And it will give this result: "Hello World; Have a nice day (from: MySelf)".

But if you mixed up the orders the result will be different.

EXECUTE Echo 'Have a nice day', 'MySelf', 'Hello World'

Will give result of "Have a nice day; MySelf (from: Hello World)"

Personally I always use the first approach. That way you will be safer from possible changing of parameter orders (whether intentionally or not) in the future.