Do I need to add "Go" or "Return" at end of SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Do I need to add "Go" or "Return" at end of SP?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-09-22 : 10:06:11

I created a store porcedure to retrieve data for ASP.NET.
Do I need to add "Go" or "Return" at end of SP?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-22 : 10:16:46

If the sql code (that contains the SP code) has other statements besides the SP code then you need to encapsulate the SP code within GO statements. A "create procedure" statement(s) needs to be in its own "batch". A RETURN statement is not required. Here is an example of why you may want to use both keywords:


if object_id('dbo.myProc') > 0
       drop proc dbo.myProc
GO

create proc dbo.myProc --create needs to be first statement in batch
as

....

begin try
       ....
end try
begin catch
       goto onError
end catch

return 0

onError:
--error handler code
return -1

go  --end of batch means end of Procedure
grant exec on dbo.myProc to public
go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources