Declaring stored procedures with variables after AS or after BEGIN

Question

I've found that you can declare the working variables for a stored procedure after the AS and after the BEGIN. For example:

Create Procedure pr_DoAnUpdate 
  (@psInputString varchar(60))
As
Declare @sSomeVariable varchar(10);
Begin
  Do the update
End

or

Create Procedure pr_DoAnUpdate 
  (@psInputString varchar(60))
As
Begin
  Declare @sSomeVariable varchar(10);

  Do the update
End

My question is, is there any standard that dictates where it should go or are there any performance issues to take in to account when doing it either way?

Answer 1

In SQL Server, a stored procedure is declared as CREATE PROCEDURE [parameters] AS [body], with [body] the entire rest. The BEGIN/END you usually see are just there for show, it's a block of statements regardless. This is a source of many errors:

CREATE PROCEDURE InsertStuff(@Name NVARCHAR(10)) AS
BEGIN
    INSERT Stuff(Name) VALUES (@Name);
END;

-- remove test data
TRUNCATE TABLE Stuff;

If you think you just created a stored procedure and then truncated a table, you're wrong. Instead, you just created a stored procedure that truncates a table, because it's become part of the body. Oops.

The two forms you mentioned are functionally equivalent. There is no difference in execution time either. For sanity's sake, though, you should stick to the AS BEGIN; [statements] END;, it's confusing enough as it is.