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.