Collections:
Ending Stored Procedures Properly in SQL Server
How To End a Stored Procedure Properly in SQL Server Transact-SQL?
✍: FYIcenter.com
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 executed. It was included as part of the stored procedure "ShowFaq". This is why you were getting the error "Invalid object name 'Faq'."
⇒ Generating CREATE PROCEDURE Scripts on Existing Stored Procedures in SQL Server
⇐ Creating Stored Procedures with Statement Blocks in SQL Server
2017-01-05, 10851🔥, 0💬
Popular Posts:
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...
How To Find Out What Privileges a User Currently Has in Oracle? Privileges granted to users are list...
How To Revise and Re-Run the Last SQL Command in Oracle? If executed a long SQL statement, found a m...
What is SQL Server Transact-SQL (T-SQL)? SQL Server Transact-SQL, also called T-SQL, is an extension...
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...