What line does the error line number refer to?

You’ve got an error and it gives you a line number

Msg 50000, Level 16, State 1, Procedure TestToSeeWhatTheErrorLineNumberRefersTo, Line 14

Some Error

What line does that actually refer to?

Well its the line in the original batch that compiled the procedure and so in the case below it includes the comments and the white space before it.

Try it for yourself

go

/*

Some random comments about how great sqlbits is just to fill some space

 

Make sure you come along to York at the end of September

 

www.sqlbits.com

 

:)

*/

create procedure  TestToSeeWhatTheErrorLineNumberRefersTo

as

raiserror ('Some Error',16,1)

 

go

exec TestToSeeWhatTheErrorLineNumberRefersTo

It gets interesting when you use dynamic SQL. Change the procedure as follows

 

 

/*

Some random comments about how great sqlbits is just to fill some space

 

Make sure you come along to York at the end of September

 

www.sqlbits.com

 

:)

*/

alter procedure  TestToSeeWhatTheErrorLineNumberRefersTo

as

execute ('raiserror (''Some Error'',16,1)')

 

go

You should find that you now get the following error.

Msg 50000, Level 16, State 1, Line 1

Some Error

Note two things the line number has changed AND it no longer refers to the stored procedure. Thats because you no longer are in the scope of the stored procedure but a lower scope.

This is why when ever I use dynamic SQL I always add a debug statement and PRINT the SQL that is going to be executed.

alter procedure  TestToSeeWhatTheErrorLineNumberRefersTo

@debug int =0

as

declare @sql nvarchar(1000)

set @sql = '/* some comment or other code

more comments

*/

raiserror (''Some Error'',16,1)'

 

if @debug > 0

  print @sql

 

exec sp_executesql @sql

This allows you to get the SQL statement that was executed and figure out which line it refers to. This is especially useful when you are adding where clauses and joins to your SQL and so trying to figure out what line the error refers to is important.

Did you know that if you double click on an error in management studio it tries to take you to the line that failed. This only works well if you are running a script in management studio, it doesn’t take you to the correct line if you are executing stored procedures.

Published Thursday, July 15, 2010 2:45 PM by simonsabin
Filed under: ,

Comments

# Twitter Trackbacks for What line does the error line number refer to? - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 What line does the error line number refer to? - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

Thursday, July 15, 2010 3:46 PM by SimonS Blog on SQL Server Stuff

# What line does the error line number refer to?

You’ve got an error and it gives you a line number Msg 50000, Level 16, State 1, Procedure TestToSeeWhatTheErrorLineNumberRefersTo