How to interpret this SQL Server error message

Question

I am executing a stored proc from SSMS and I get this error message:

Msg 295, Level 16, State 3, Procedure spEdiCreateOrders, Line 80 [Batch Start Line 2]
Conversion failed when converting character string to smalldatetime data type.

Of course I could try and err, but I'd like to be able to take advantage of the message, and I am a bit perplexed by the message.
'line 80': starting from where ? From the BEGIN clause of the Alter procedure ? Or does it really refer to the line 80 I see when I click Modify ?
Also, what does Batch Start Line 2 mean ?
Thx !

---

edit: note that I am NOT interested in help solving the issue (it is already solved). I just want a clear guidelines about how to interpret 'line 80' and 'Batch Start Line 2', so that next time I immediately know where to look at.

Solution 1

When looking at the code of a stored procedure using the Alter procedure, the error line is from the BEGIN command, as the error occurred in the procedure, not in the Alter procedure command

• BEGIN = line 0
• next line (the actual start of the procedure = 1
• etc

Batch line 2 means the second batch of statements - this means you have a GO somewhere in the procedure, and this is in the second set of statements (http://sqlhints.com/tag/examples-of-sql-go-statement/), the first set appearing before the GO

Solution 2

About Line n

N it is refer to the number of line of object considering any spaces with in it , or before its body.

Example 1

Assume the next is SQLQuery window after clicking New Query button in SSMS

1. Create proc spTest1

2. As

3. Begin

4. Select 1/0

5. End

after executing spTest1, you will get the next message

Msg 8134, Level 16, State 1, Procedure spTest1, Line 4

Example 2

Assume the next is SQLQuery window after clicking New Query button in SSMS

1. 

2.

3.

4.

5.

6. Create proc spTest2

7. As

8. Begin

9. Select 1/0

10. End

after executing spTest2, you will get the next message

Msg 8134, Level 16, State 1, Procedure spTest2, Line 9

Conclusion how get the accurate number

For getting the accurate number use the system stored procedure sp_helptext as next

Exec sp_helptext spName

Comments

[SeanC]

when looking at the procedure using an Alter command, then you need to look at the code of the procedure itself, not the extra parts SQL Server put in place to allow you to edit the procedure

[Jing He]

I just tested, Batch Start Line 2 actually means you have a "GO" command in the 2nd line, so the current batch start from line 2.