Posted by theintrepidfox on 05/19/06 04:26

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

• Next in forum: Insert into a table recursively
• Prev in forum: Re: SQL Server 2000 running out of space.
• Thread view: SQL Script Debugging - Just being curious

[Reply to this message]