Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Ending Stored Procedures Properly

By: FYIcenter.com

(Continued from previous topic...)

How To End a Stored Procedure Properly?

Where the end of the "CREATE PROCEDURE" statement structure? The answer is simple, the end of the statement batch.

Even if you are using a "BEGIN ... END" statement block, the stored procedure structure is not going to end at the end of the statement block. It will continue to the end of the statement batch, usually the GO command. The tutorial exercise gives you a good example:

USE FyiCenterData;
GO

DROP PROCEDURE ShowFaq;
DROP TABLE Faq;
GO

-- How this statement batch will be executed?
CREATE PROCEDURE ShowFaq AS BEGIN
   PRINT 'Number of questions:';
   SELECT COUNT(*) FROM Faq;
   PRINT 'First 5 questions:'
   SELECT TOP 5 * FROM Faq;
END;
CREATE TABLE Faq (Question VARCHAR(80));
GO

EXEC ShowFaq;
GO
Number of questions:
Msg 208, Level 16, State 1, Procedure ShowFaq, Line 3
Invalid object name 'Faq'.

What happened here was that the "CREATE TABLE" statement was not execueted. It was included as part of the stored procedure "ShowFaq". This is why you were getting the error "Invalid object name 'Faq'."

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