Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Creating Stored Procedures with Statement Blocks

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What Are Stored Procedures?
  2. How To Create a Simple Stored Procedure?
  3. How To Execute a Stored Procedure?
  4. How To List All Stored Procedures in the Current Database?
  5. How To Drop an Existing Stored Procedure?
  6. How To Create a Stored Procedure with a Statement Block?
  7. How To End a Stored Procedure Properly?
  8. How To Generate CREATE PROCEDURE Script on an Existing Stored Procedure?
  9. How To Get the Definition of a Stored Procedure Back?
  10. How To Modify an Existing Stored Procedure?
  11. How To Create Stored Procedures with Parameters?
  12. How To Provide Values to Stored Procedure Parameters?
  13. What Are the Advantages of Passing Name-Value Pairs as Parameters?
  14. Can You Pass Expressions to Stored Procedure Parameters?
  15. How To Provide Default Values to Stored Procedure Parameters?
  16. How To Define Output Parameters in Stored Procedures?
  17. How To Receive Output Values from Stored Procedures?
  18. How To Create a Local Temporary Stored Procedure?
  19. Can Another User Execute Your Local Temporary Stored Procedures?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...