Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not ending Transact-SQL statements with a semicolon Expand / Collapse
Author
Message
Posted Tuesday, May 22, 2012 10:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:38 PM
Points: 2,290, Visits: 2,545
Hello,

I'm in the process of planning an upgrade from SQL 2000 and SQL 2005 to SQL 2008 R2. I was wondering if anyone has a good procedure for checking the existing SQL 2000 and SQL 2005 databases for this deprecated feature warning: Not ending Transact-SQL statements with a semicolon. We have tons of SQL code; does this mean we have to find every single statement in those tons of code and add a semicolon after each statement in it? Does that include SET and GO statements as well, in other words do they have to be modified to, say, SET ANSI_NULLS ON; and GO; ? If so, does anyone have any scripts or recommended tools to make this easier?

Thanks for any help!

-webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1304273
Posted Tuesday, May 22, 2012 10:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,507, Visits: 30,223
As for the GO, no, do not put a ; after it as it will error.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1304284
Posted Tuesday, May 22, 2012 10:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
Hmm... Why do you think that every statement needs to end with a semi-colon in 2008R2? Also, GO is a batch terminator and will never require a semi-colon.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1304285
Posted Tuesday, May 22, 2012 10:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,507, Visits: 30,223
At some point in time, yes you may need to end every statement with a ;. Until then, I believe the only statements that require that the previous statement be terminated is the WITH (CTE) and MERGE statements. You should look at the new features added in SQL Server 2005, SQL Server 2008 to determine if any others may require this. If not, don't worry about. In new development, get the developers used to terminating the statements with a ;, it will make things easier going forward.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1304301
Posted Tuesday, May 22, 2012 11:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
You don't currently have to terminate statements with a semicolon. However, you will have to at an as-yet-undetermined future date.

I don't know a tool that will automate adding them for you. Probably, once it's needed, someone (maybe RedGate) will create such a tool, but there's no market for it yet.

As already mentioned, GO won't need one. It's not an SQL statement, it's an inline command for Management Studio (and for old Query Analyzer). You can actually put a number after GO to get it to run the preceeding batch X number of times. Like "GO 10" will run the batch 10 times. Not many seem to know that. Putting a semicolon after GO makes the compiler think you are trying to do an SQL command, and will throw an error.

SET will need one, at whatever future date they become mandatory. So will BEGIN and END. (END makes sense to have one, but BEGIN doesn't. Not to me anyway.) There are some others that won't as well.

Example:

CREATE PROCEDURE dbo.MyProc
AS
SET NOCOUNT ON ;

SELECT *
FROM dbo.MyTable ;

Note that the CREATE PROC <name> AS statement doesn't have a semicolon after it, but SET NOCOUNT ON and the SELECT statement do.

None of that is needed, yet. But it will be soon, so it's a good idea to get in the habit now.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1304338
Posted Tuesday, May 22, 2012 11:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:38 PM
Points: 2,290, Visits: 2,545
Thanks, everyone, for your replies. Especially for the information about GO, which I didn't know.

I guess for now, I will make this a longer-term task with a checklist for modifying the code one database at a time. It would be nice to have this done before migrating but completing all of it may not be possible given my schedule.

Thanks again,
webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1304342
Posted Tuesday, May 22, 2012 11:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 32,781, Visits: 14,942
Just an FYI, I tend to use semi-colons separately. Just for ease of editing and working.

alter database MyDb set read_only
;
go
select name
from sys.databases
where name not in ('master', 'msdb')
;

That way if I need to edit things, it's easy with the semi-colon on the next line.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1304360
Posted Tuesday, May 22, 2012 12:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:38 PM
Points: 2,290, Visits: 2,545
Hi Steve,

Thanks for the tip! That's a great idea.

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1304373
Posted Tuesday, May 22, 2012 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 22,507, Visits: 30,223
I've done that on many occasions, especially while writing a query. Makes sure that I have the final ;.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1304378
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse