T-SQL Database Development Stored Procedures (3 of 4)

3. T-SQL Database Development Stored Procedures (3 of 4)

Hey Now,

This is the third T-SQL post of four to provide a stellar understanding of database development using T-SQL.
We'll discuss Stored Procedures. Stored Procedures are very common & have many strengths. There are many other ways to access data such as ORM (Object Relationship Models) however the overwhelming majority of my experience has been with Stored Producers. Stored Procedures are located in the Object Explorer file tree when the database node is expanded then the Programmability node is expanded we can expand the Stored Procedures node.

3. Stored Procedures
  a. Create
  b. Database Definition (Use)
  c. Comments
  d. Common Settings
  e. Begin End
  f. Alter / Modify
  g. Drop
  h. Rollback
  i. Grant Permission
  j. Execute SP
  k. Parameters

a. Creating SP's
Creating a SP is some nice code for us to continue with. Below is an example of how to create an SP named p_getNames which will select name from our table [newTablePeopleName]. Once the script is placed in the Query window and executed the sp will be created.
-- Following Script will Create sp named p_getNames to select all records from table newTablePeopleName
CREATE PROCEDURE p_getNames
AS
Select nameOfPerson
From [newTablePeopleName]
Additional items we can add to SP's can be: Database Definition, Comments, Check if Exists Drop, Common Settings, Begin End, Grant Permissions, Execute SP, GO, Rollbacks

b. Database Definition - At the top of the script SP's can include a USE command to define the database
Use [databaseName]
GO

c. Comments
---- Comments below are some examples
/****** Object: Stored Procedure [dbo].[p_getNames] Script Date: 02/13/2010 09:23:57 ******/
-- =============================================
-- Create p_getNames stored procedure
-- =============================================
-- ======================================================
-- Author: <Catto>
-- Create date: <2.13.10>
-- Description: <Select Names nameOfPerson from table newTablePeopleName>
-- =======================================================
-- ALTER p_StoredProcuresName

d. Common Settings (Three) Included in many scripts toward the top:
ANSI_NULLS - Good settings for select & null and retuning zero
--- Example of setting ANSI_NULLS
SET ANSI_NULLS ON
GO
QUOTED_IDENTIFIER - deals with single & double quotes.
-- example of setting QUOTED_IDENTIFIER
SET QUOTED_IDENTIFIER ON
GO
No Count - Option Used after 1st begin in SP Example below:
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

e. BEGIN END - Often used in SP's, after the AS & before the last GO
-- ex. of BEGIN & END in SP scrip
CREATE PROCEDURE p_getNames
AS
BEGIN
SELECT nameOfPerson
FROM [newTablePeopleName]
END
GO

f. Alter SP's - Once the proc is created if there are any changes we can alter the SP. Below is an example how we could alter a proc:
-- ex. Following Script will Alter the sp named p_getNames to select the tableid column not the name from table newTablePeopleName
ALTER PROCEDURE p_getNames
AS
BEGIN
Select newTableid
--Select nameOfPerson
From [newTablePeopleName]
END

g. DROP - Dropping a procedure is basically deleting the object from the db. In the following example we'll drop the sp we created:
/****** Object: Stored Procedure [dbo].[p_getNames] Script Date: 02/13/2010 11:06:34 ******/
DROP PROCEDURE [p_getNames}
GO

h. Rollback
Rollback - Script can be included at the bottom of the sp all commented out as an old stone chisel type of a version control to have a backup of a working copy of the SP.

i. Grant Permissions
Granting permissions is a less common task yet it’s still important. Once a procedure is created depending how the database is setup we may need to grant permissions to users. In sp’s we often will grant the ‘execution’ permission.
-- Grant Execute Permission for the proc to a user
GRANT EXECUTE on p_getNames to dbUserName

j. Execute Stored Procedures
Once the proc is created to use it we will execute the procedure. One way to execute the proc is in Object Explorer we can right click on the sp an click execute. This will open an ‘Execute Permission’ window kinda like a design view window, if there are parameters we can enter in values for the parameters then click the OK button and a new query window will open with the script created. We could also open a query window and use the following script.

-- Simple script to execute sp
Exec p_getNames

k. Parameters – Parameters are values passed into the stored procedure. This give us powerful ability in the procedure to query data. Often times we want to query data depending on values we provide. It’s critical to the use of SP's is to be able to pass data into the SP. Below is an example of a Stored Procedure with a parameter passed into it.
-- Example displays the use of passing in a parameter into the SP
ALTER PROCEDURE p_getNames
(
@tableID numeric
)
AS
BEGIN
Select nameOfPerson
From [newTablePeopleName]
WHERE newTableid = @tableID
END

Stored Procedures are very common & powerful. They are not EF4.0 or other ORM’s but they are very prominent way of accessing data. It's great for us to have an understanding, develop & use them.

What do you think of Stored Procedures?

Here are the other post in the series:
1 T-SQL Database Development IDE & Queries (1 of 4)
2 T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

Later,
Catto