1

Ok I typed this to google and no answer

Here comes my question

Assume that inside a stored procedure I have the following selects would it make any difference putting ; at the end of select statements or not?

I mean if I do not put ; at the end of statement what happens? And if I put what happens?

begin

declare @param_1 int;
declare @param_2 int;

select @param_1 = col1 from mytable
select @param_2 = col2 from mytable

end

Case 1 above no ; at the end

begin

declare @param_1 int;
declare @param_2 int;

select @param_1 = col1 from mytable;
select @param_2 = col2 from mytable;

end

Case 2 above ; at the end

I am not asking for this particular case. It will obviously have no effect on this example but in run time what difference does it make ? I mean does it complete it then move next or what

Thanks for the answers

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Furkan Gözükara
  • 22,964
  • 77
  • 205
  • 342
  • `;` is the standard for terminating a statement. Without it, your lines will all be considered a SINGLE statement - sql doesn't consider linebreaks to be significant. – Marc B Aug 25 '14 at 15:54
  • 2
    Some statements like `Merge` and `With` require a `;` at the end or right before the statement. Essentially, it's terminating the preceding statement(s). There is no difference in these examples. – Siyual Aug 25 '14 at 15:55
  • Check http://msdn.microsoft.com/en-gb/library/ms143729.aspx in some future version of SQL Server, semicolons will be required always. (It may be some time, though...) – Laurence Aug 25 '14 at 16:04
  • Semicolon is a (mostly optional) *syntax* element. It has no effect on execution or peformance, only on parsing the syntax. – RBarryYoung Aug 25 '14 at 16:07
  • in the environment of the SQL Analyzer or SQL Server management studio, the `;` is almost optional. However when executing some continuous queries/commands (all in a string) such as in `C#` code, you do need to use the `;` to separate the queries/commands. – King King Aug 25 '14 at 16:07
  • @KingKing: Can you back that up with an example? – Andomar Aug 25 '14 at 16:13
  • @Andomar I've experienced with this before, such as there are some queries in a string like this `"create table table1 (...) create table table2 (...) create table table3 (....) ...."`, you do need the `;` to separate the `create` like this `"create table table1 (...) ; create table table2 (...) ; create table table3 (....); ...."` and such as use `SqlCommand.ExecuteNonQuery()` to run it. It's a long time ago. – King King Aug 25 '14 at 16:16

3 Answers3

4

The ; terminates a statement. In most cases it's optional. Whether you terminate a statement implicitly (without a semicolon) or explicitly (with a semicolon) makes no difference. MSDN says:

Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

Here's an example where ; is not currently optional. This will fail:

select 6
with t1 as (select 7 as col1) select * from t1

While this will work:

select 6
; with t1 as (select 7 as col1) select * from t1

A with or merge statement requires the preceding statement to be explicitly terminated.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • `... it will be required in a future version` - Sure it will. How many thousands of hours of modifying code would that require for everyone? I don't see that ever happening. – Dave.Gugg Aug 25 '14 at 16:11
  • So we can say that it is better to always use ; - can there be any scenarios that we would want to avoid ? – Furkan Gözükara Aug 25 '14 at 16:29
  • 1
    `;` is always okay, but there is no benefit in using it where it is optional – Andomar Aug 25 '14 at 16:31
  • @MonsterMMORPG there is no downside to using it, so, as a good habit for writing portable code, it is always better to use it. – Kristen Hammack Aug 03 '16 at 12:29
1

As I understand it, it means that the statement ending with the ';' must complete/finish/terminate before the next line will begin executing.

How 'bout a Fresca
  • 2,267
  • 1
  • 15
  • 26
  • True, but just a side effect of terminating statements. An implicitly terminated statement (without a semicolon) has to finish too before the next statement can begin executing. – Andomar Aug 25 '14 at 16:28
1

Not using a semicolon to terminate statements is a deprecated behavior (http://msdn.microsoft.com/en-us/library/ms143729.aspx). Whether or not it will actually be disallowed in future versions is irrelevant; it is deprecated and therefore should not be done. All statements should use a semicolon as a terminating character. Further, using semicolons help code readability.

Katherine Elizabeth Lightsey
  • 853
  • 5
  • 12