Dear Group

Something that I ever found quite difficult was finding a bug in a
script e.g. in a stp#ored procedure as often it would indicate the
error in a completely different line in which the error is actually
located. Take the following script

ALTER PROCEDURE [dbo].[fra_UpdateCompany]
@CompanyID int, @CompanyName varchar(50), @Status int, @TelNo
varchar(50), @FaxNo varchar(50), @Email varchar(50), @Web varchar(50),
@OfficeType int, @Comment varchar(512)
AS
DECLARE
@CommentOrg varchar(512)

IF (LEN(@CompanyName) < 1) BEGIN SET @CompanyName = NULL END
IF (LEN(@TelNo) < 1) BEGIN SET @TelNo = NULL END
IF (LEN(@FaxNo) < 1) BEGIN SET @FaxNo = NULL END
IF (LEN(@Email) < 1) BEGIN SET @Email = NULL END
IF (LEN(@Web) < 1) BEGIN SET @Web = NULL END
IF (LEN(@Comment) < 1) BEGIN SET @Comment = NULL END

SET @CommentOrg = (SELECT Comment from fra_company WHERE CompanyID =
@CompanyID)
SET @Comment = (@Comment + '' + @CommentOrg)

-- UPDATE COMPANY
UPDATE fra_company SET CompanyName = @CompanyName, Status = @Status,
TelNo = @TelNo, FaxNo = @FaxNo, Email = @Email, Web=@Web, OfficeType =
@OfficeType, Comment = LTRIM(@Comment) WHERE CompanyID = @CompanyID

It throws an error in Line 17 IF (LEN(@TelNo) < 1) BEGIN SET @TelNo =
NULL END when the error is actually in line 23: SET @CommentOrg =
(SELECT Comment from fra_company WHERE CompanyID = @CompanyID)

ErrorMsg:
Msg 137, Level 15, State 2, Procedure fra_UpdateCompany, Line 17
Must declare the scalar variable "@ContactID".

Why is that? I had hoped that it had improved from SQL 7.0 to 2005 but
it's still the same vague thing.

Thanks for sharing your expertise and wisdom on this,

Martin

(theintrepidfox@hotmail.com) writes:
> It throws an error in Line 17 IF (LEN(@TelNo) < 1) BEGIN SET @TelNo =
> NULL END when the error is actually in line 23: SET @CommentOrg =
> (SELECT Comment from fra_company WHERE CompanyID = @CompanyID)
>
> ErrorMsg:
> Msg 137, Level 15, State 2, Procedure fra_UpdateCompany, Line 17
> Must declare the scalar variable "@ContactID".
>
> Why is that? I had hoped that it had improved from SQL 7.0 to 2005 but
> it's still the same vague thing.

SQL Server is not very good at error messages. In some cases the errors
are flagged on the next statement. And how nice isn't when you have a 50+
lines statement with a misspelled column name, and you only get the line
number where the statement starts!

I've submitted a request for improvements on
http://lab.msdn.microsoft.com/produc...e-3d6addb69f5d
go and vote for it if you like!

That said, I've never seen anything as bad as in your example, so I suspect
that there is something more to it. And I cound find any undeclared
@ContactID in you example...


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks Erland. You answer is very appreciated. I admit it's a pretty
bad example.. but then it was just a sample. Enjoy your weekend :-)