When to use Stored Procedures in SQL Server

 SQL Server

You sometimes will be asked if you should create a stored procedure to perform a specific action. Stored Procedures can be used to do any CRUD operation. CRUD represents an acronym for the database operations Create, Read, Update, and Delete. There is no certain limitation to what you do with a Stored Procedures, only specific scenarios that make sense in your unique environment.

  • To do a series of DML (Data Manipulation Language) operations.
  • Suitable for a maintenance standpoint type of applications since troubleshooting might be easier if the logic is in one place.
  • Stored Procedures can reduce SQL Injection attacks.
  • A Stored Procedure separates the data layer from the application layer. When you implement an application a database designer/developer can manage the database objects while other traditional developers can focus on the program logic or application appearance.
  • It helps security, by managing  access for tables and other database objects based on user role.
  • SQL Server has built-in functions to do most everything you need, so you might not have to write as much code
  • If you are good at Transact-SQL, creating Stored Procedures for an application is fast and simple.

So you want to see an example? The simple Transact-SQL code would be like the example below, which will return all rows from this imaginary table.

SELECT * FROM MyDatabase.CustomerAddress

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

CREATE PROCEDURE GetCustomerAddress
AS
SELECT * FROM MyDatabase.CustomerAddress
GO

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

EXEC GetCustomerAddress
--or just simply
GetCustomerAddress

You can read more about SQL Server stored procedures here.

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