SQL Server Stored Procedure

http://www.mssqltips.com/tutorial.asp?tutorial=160

SQL Server Stored Procedure
(Introduction)

Overview
A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.

In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server.

You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.

Different options for creating SQL Server stored procedures

Overview
There are various options that can be used to create stored procedures. In these next few topics we will discuss creating a simple stored procedure to more advanced options that can be used when creating stored procedures.

Explanation
Some of the topics we will cover include:

Creating a simple stored procedure
(CREATE PROCEDURE)

Overview
As mentioned in the tutorial overview a stored procedure is nothing more than stored SQL code that you would like to use over and over again. In this example we will look at creating a simple stored procedure.

Explanation
Before you create a stored procedure you need to know what your end result is, whether you are selecting data, inserting data, etc..

In this simple example we will just select all data from the Person.Address table that is stored in the AdventureWorks database.

So the simple T-SQL code would be as follows which will return all rows from this table.

SELECT * FROM AdventureWorks.Person.Address

To create a stored procedure to do this the code would look like this:

CREATE PROCEDURE uspGetAddress

AS

SELECT * FROM AdventureWorks.Person.Address

GO

To call the procedure to return the contents from the table specified, the code would be:

EXEC uspGetAddress

–or just simply

uspGetAddress

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s