By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

SQL Script Debugging - Just being curious

P: n/a
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

May 19 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
(th************@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, es****@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
May 19 '06 #2

P: n/a
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 :-)

May 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.