Back to basics: Using Stored Procedures – Developer Concepts

Site Loading

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