Back to basics: Using Stored Procedures

This is the second in a series on getting back to basics,  aimed at those who need help with some fundamentals in software development.  This post shows how to create, alter and run a stored procedure in SQL:

Benefits of Stored Procedures:

• Speed: the SP is cached on the server.
• One location for Data access
• Security,  the DBA can restrict access to those who really need the data.

Create Syntax:

CREATE PROCEDURE MySpName
(
ParamName INT -- input param for MySpName
)
AS
SELECT ColName FROM MyTable Where ColName = ParamName
GO

Alter Syntax:

ALTER PROCEDURE MySpName
(
ParamName INT -- input param for MySpName
)
AS
SELECT ColName FROM MyTable Where ColName = ParamName
GO

Delete Syntax:

DROP PROCEDURE MySpName

Execute Syntax:

Execute MySpName 1