SQL Server FAQ - Creating Stored Procedures with Statement Blocks

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Create a Stored Procedure with a Statement Block?

If you are creating a stored procedure with multiple statements, it's better to use "BEGIN ... END" to group all statements into a single statement block.

The tutorial exercise below shows you some good examples:

USE FyiCenterData;
GO

CREATE PROCEDURE Show AS BEGIN
   SELECT name, type_desc FROM sys.tables;
   SELECT name, type_desc FROM sys.views;
   SELECT name, type_desc FROM sys.procedures;
END;
GO
Command(s) completed successfully.

EXEC Show;
GO
name                type_desc
------------------- ---------------------
fyi_random          USER_TABLE
fyi_links_indexed   USER_TABLE
fyi_links           USER_TABLE
fyi_links_copy      USER_TABLE

name                type_desc
------------------- ---------------------
fyi_links_top       VIEW
fyi_links_dump      VIEW
fyi_links_view      VIEW

name                type_desc
------------------- ---------------------
Hello               SQL_STORED_PROCEDURE
date                SQL_STORED_PROCEDURE
Show                SQL_STORED_PROCEDURE

(Continued on next topic...)