SQL For Programmers - Stored Procedure Basics

September 16, 2008 By: Dave

trav-035 Now that we’ve established a reason for using them, let’s continue with the basic syntax of the stored procedure. I think you’ll find that it is not that dissimilar to writing a procedure in any other language. Although, as we move on, I think you’ll find that the syntax we have to use to get anything done is quite cryptic.

The first thing that makes any procedure a procedure is the ability to define the procedure name and define the parameters that can be passed. This is also true of stored procedures.

The basic structure of a stored procedure looks like:

span style="color: blue;">CREATE PROCEDURE procedureName
(
/* Parameters go here */
)
AS
/* Body of the Procedure
   goes here. */

If you later needed to change this procedure, you would use this syntax:

ALTER PROCEDURE procedureName
(
/* Parameters go here */
)
AS
/* Body of the Procedure
   goes here. */

If you don’t have any parameters to pass, you can leave out the parentheses like so:

CREATE PROCEDURE procedureName
AS
/* Body of the Procedure
   goes here. */

You can define parameters to your procedure using comma delimited lists of @ prefixed names. You can name them whatever you want. Also keep in mind that SQL is not case sensitive. When you define the variables, you will also need to specify the type and, optionally, the size of the variable.

So to pass in a string and an integer, you would pass in:

an style="color: blue;">CREATE PROCEDURE procedureName
(
   @StringVar as VARCHAR,
   @IntVar as Int
)
AS
/* Body of the Procedure
   goes here. */

If you wanted to limit the size of the string to 50 characters, you would use:

CREATE PROCEDURE procedureName
(
   @StringVar as VARCHAR(50),
   @IntVar as Int
)
AS
/* Body of the Procedure
   goes here. */

Then, if you wanted to use those variables as part of your where clause, you would use:

CREATE PROCEDURE procedureName
(
   @StringVar as VARCHAR(50),
   @IntVar as Int
)
AS
   SELECT fieldOne, fieldTwo, etc FROM tableName
   WHERE stringField=@stringVar AND intField=@intVar

You can find a full list of datatype definitions at the Microsoft site:

http://msdn.microsoft.com/en-us/library/ms187752.aspx

By default, the parameters are input only. I’ve found that that is all I need. But you may need to return a value through a parameter for some reason. You can do that by defining the parameter as an OUTPUT parameter:

an style="color: blue;">CREATE PROCEDURE procedureName
(
   @StringVar as VARCHAR(50),
   @IntVar as Int OUTPUT
)
AS
/* Code that does something
   meaningful */
   SELECT @IntVar = 20

Notice you have to assign the output parameter a value.

All stored procedures either return nothing or they return a set of rows. So a better way of writing the stored procedure so that it returns 20 is to use something like this:

span style="color: blue;">CREATE PROCEDURE procedureName
(
   @StringVar as VARCHAR(50),
   @IntVar as Int OUTPUT
)
AS
/* Code that does something
   meaningful */
   SELECT 20 as intField

where intField could be any meaningful name that could be used as a column name.

Other post in SQL For Programmers