Site Loading

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
Close