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