Semi-Colons in Stored Procedures

Question

I hope I'm not opening up Pandora's Box here...but I have read many different viewpoints on using semi-colons (;) within Stored Procedures. In one comment I even saw that down the road, SQL Server is going to require them! So where should semi-colons go in my stored procedure? After each (-) in the Procedure?And by minus (-) I am referring to blocks created within SQL Server Management Studio. That's where I'm putting my semi-colons anyways.

Your thoughts...

Thanks for your review and am hopeful for a reply.

PSULionRP

Answer 1

Add ; to each executable statement (e.g. select and SET). Don't add semicolon to conditional statements and BEGIN/END statements. Practically to everything else you may want to add them, e.g.

declare @a int ;

set @a = 1;

select ... from myTable where ... ;

IF @a=1 -- no semicolon here

   begin -- not here

      set @b = @b++;

   end

else

   begin

      select ... ;

   end

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I hadn't really thought about it until now (although I have a vague recollection of hearing about it), but yes, at some point we will have to start using semi-colons.  A quick Google search brings up a few blogs about it, and straight from the horse's mouth:

http://msdn.microsoft.com/en-us/library/ms143729.aspx

With that in mind, it is probably a good idea to start using them.  I have never used them except before a CTE, so I guess I need to get used to it too.

Directly on topic, would we need them on insert/update/delete/merge statements as well?

Insert MyTable
Values (1,1,1,1);

Update MyTable
Set MyColumn = 1;

Delete MyTable;

I would assume so by "each executable statement", but, you know....

Edit:

Also, here is an old blog post by Itzik Ben-Gan on the subject: http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/semicolon-140706

Answer 3

Its a good practice to start putting semicolon because with SQL 2012 statements not ending with semicolon is deprecated(marked for removal)

here is a nice article ,

http://www.sqlskills.com/blogs/bobb/yet-another-semicolon-rule-in-sql-server-denali/

 especially the last part of the article says

And in Denali, it occurs again. The new "THROW" statement has the same requirement as "WITH"; if its not the first statement in the CATCH block, the statement before it must end with a semicolon. Same thing if THROW is used to throw an exception in a batch, a la RAISERROR, then the statement before it must end with a semi. And, just in case you decide to put a semicolon after everything that looks like a T-SQL statement, an interesting thing about TRY-CATCH blocks. I can put a semicolon after BEGIN TRY, BEGIN CATCH, or END CATCH without incident. But putting a semicolon after END TRY causes an error.

---

Satheesh

Answer 4

I don't know whether or not I've seen an exact rule.  But basically, you can put ;'s at the end of everything that you think of as a complete command.  So the following code is legal

declare @i int;
declare @j int;
begin try;
  if @i Is Null
  begin;
    set @i = 5;
  end;
  else
    select * from sys.objects;
end try
begin catch;
  set @i = 0;
end catch;

In the above code, you are not allowed to put a ; after the IF @i IS NULL or after the ELSE because those must be followed by a statement that does something and you can't put one after the END TRY because END TRY must be immediately followed by BEGIN CATCH.

If I was actually writing the above code, I would not put ;'s after the BEGIN, the BEGIN TRY, or the BEGIN CATCH because it feels to me like they are the beginning, not the end of something.  But putting them there is legal. 

My advice would be to just start putting them where it feels to you that this is the end of a SQL command.  That will be right 99.9% of the time and if you put one where it can't go you'll get a syntax error.  It won't be long until it feels natural and you don't even need to think about it anymore.

Also, don't worry about the dire warnings that somewhere down the road, Microsoft will require them everywhere.  That would break literally millions and millions of lines of code around the world and Microsoft will not do that.  What Microsoft will do is as they introduce new capabilities (like cte's or the MERGE statement), Microsoft is likely to require semicolons to use those new capabilities.  So, use semicolons as you write new code, but do not worry about going back and changing existing code. 

Tom

Answer 5

Yeah, slowly but surely the semicolon from procedural languages such as C are forced upon the declarative language SQL. After the required semicolon in relation to Common Table Expressions (CTEs) there is no getting around them. After that, it doesn't take long for people to start calling it a good practice to use them all the time.

Maybe I am just a grumpy dinosaur, but to me, the semicolon in SQL is one more useless character that I tend to avoid when possible. I will use it when I have to. I am sure that many people will consider the following notation a poor practice

;WITH my_CTE AS (
  SELECT ...
)
SELECT ...
FROM my_CTE

Why "they" try to transform SQL into C# at every possible moment (next version) is beyond me...

---

Gert-Jan

Answer 6

I don't think they are doing this to be arbitrary or because they want it to look like C.  Semicolons were a part of ANSI SQL from the beginning and even in T-SQL they have always been optional.

Of course, if they are always optional (as they were for many years), people (including me) just never used them.  Not using them made sense, why add a meaningless extra character to almost every line of code.

This worked fine for a long time.  But the ANSI standard evolved and got many new features (like CTE's) and we in the T-SQL community wanted the new features.  (Please note that some of the following is speculation - I have no connection with Microsoft and the developers of the SQL parser.) It is my understanding when the Microsoft parser is deciding what the meaning of a given token in the batch of commands is, it looks at that token (of course) and anything that came before that token plus at most ONE following token.  This means that the parser must be able to recognize the end of a command by only looking at the next token.

One way to do this is for the language to have a set of keywords that can only be as the first token in a command and cannot be used anywhere else and to have a rule that every command must begin with one of those keywords.  Then when your parser looks at the current token, checks the next token and sees that it is one of those keywords, then the current token must be the last one in the current command.  Another way is to have a token (like ;) that is used as the last token of every command.  Then when looking at a token, the parser can determine if this is the last token just be checking the next token and seeing if it is that terminator character.  This is what ANSI SQL (and a lot of other languages did).

When Sybase was developing T-SQL, they noticed that they could make the ; optional, and still be able to parse everything in the current ANSI by only looking at the next token.  So they did, and when Microsoft took T-SQL over, they continued this.  This was going to lead to trouble because ANSI could add features that depended on the semicolon always being there and Microsoft couldn't without changing the rule about semicolons or making difficult changes to Microsoft's parser.

The first case I know of that this caused problems was when they implemented OFFSET ... FETCH.  You can use that when you want to page through rows of a result set.  Suppose you want rows 21-30 of a result, you can do

Select * From MyTable Order By MyColumn Offset 20 Rows Fetch Next 10 Rows;

And in ANSI SQL if you want the first 10 rows, the Offset clause is optional and  you can do

Select * From MyTable Order By MyColumn Fetch First 10 Rows;

But Microsoft couldn't implement that because when the parser was at the Fetch token, it could not determine what Fetch was just by looking at the next token (that is Fetch First) because it cannot tell whether the Fetch First is part of the Order By or the beginning of a Fetch statement from a cursor.  So Microsoft made the Offset required, even though it is optional in ANSI.  This was no big deal, you just use the somewhat silly looking command

Select * From MyTable Order By MyColumn Offset 0 Rows Fetch First 10 Rows;

But then came CTE's.  ANSI did them with a WITH command.  WITH is used in a lot of places in SQL, but ANSI SQL could determine that a particular WITH was the beginning of a CTE because a WITH preceded by a semicolon is a cte and a WITH preceded by anything else is part of the current command.  Under then current syntax rules (semicolons are optional), Microsoft couldn't do that.  So consider the parser trying to decide what the keyword WITH is in the following

EXEC AS LOGIN = 'Foo' WITH NO

SQL cannot tell just from looking at the following token (NO) what that WITH means.  It might be either of the following

EXEC AS LOGIN = 'Foo' WITH NO REVERT
EXEC AS LOGIN = 'Foo' WITH NO As (Select a From b) Select Max(a) From NO

The first one is not a cte, the second one is.  The proper parsing could not be determined without significant changes to the parser to look further ahead, so Microsoft implemented the rule that if a cte is not the first command, it must be preceded by a semicolon.  Similar problems caused Microsoft to require that MERGE commands be ended by a semicolon.

Foreseeing more problems like this in the future, Microsoft has said that sometime in the future, semicolons will be required everywhere.  That would be a very drastic change requiring updating much code.  So I don't think this will happen for a long time, if ever.  (It should be noted I've been wrong before.  The deimplement *= and =* for outer joins much sooner than I would have guessed).  So my current practice is to always use semicolons on new or updated code, but I'm not (yet, anyway) going to go back and change existing code.

Tom

Answer 7

I don't think they are doing this to be arbitrary or because they want it to look like C.  Semicolons were a part of ANSI SQL from the beginning and even in T-SQL they have always been optional.

No, up to SQL 6.5 they were not permitted at all.

Overall, the whole semicolon-less thing is a Sybase invention, and a bad one. But Microsoft is now making it worse, by requiring it sometimes but not always. The language becomes horribly inconsistent.

They have made a big goof in SQL 2012. Consider this typical code:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   THROW
END CATCH

It's legal, it compiles and it runs, but it does not do what you want.

Had they made THROW a reserved keyword (or just used RAISERROR), it would not have happned.

I did actually report it on Connect,
https://connect.microsoft.com/SQLServer/feedback/details/762588/urgent-to-fix-big-usability-problem-with-throw
but they closed it with a wink of the eye.

Today it's deprecated not to use semicolons - but there is no way you can verify that you are not violating the rule, so how could you take that deprecation seriously?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

I don't think they are doing this to be arbitrary or because they want it to look like C.  Semicolons were a part of ANSI SQL from the beginning and even in T-SQL they have always been optional.

No, up to SQL 6.5 they were not permitted at all.

Thanks for the correction Erland.

Tom