How To Create a Stored Procedure with a Statement Block in SQL Server Transact-SQL?

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

 

⇒Using Stored Procedures in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials