Home » SQL For Programmers » SQL For Programmers – Stored Procedure Basics

SQL For Programmers – Stored Procedure Basics

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:

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:

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:

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:

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.

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • SQL WHILE – SQL For ProgrammersSQL WHILE – SQL For Programmers The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no […]
  • Random in SQL – SQL For ProgrammersRandom in SQL – SQL For Programmers Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database.Naturally, as a programmer, I started looking for the SQL […]
  • SQL – Filtering WHERE condition on two rowsSQL – Filtering WHERE condition on two rows I received the following question a couple of days ago from a programmer using the "Ask  a Question" form which you can access from the menu of this blog:Given the following […]
  • Basic SQL Commands – SQL For ProgrammersBasic SQL Commands – SQL For Programmers Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that […]
  • SQL IF/WHILE Blocks – SQL For ProgrammersSQL IF/WHILE Blocks – SQL For Programmers OK, all you programmer types.  Here's where SQL starts getting interesting.  You have the basic concepts of declaring variables, setting up stored procedures, and doing a basic insert, […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.

One Pingback/Trackback