Error message in stored proc

Question

Post reply

Error message in stored proc

ramadesai108

SSChampion

Points: 10609
More actions
December 22, 2008 at 2:06 pm

#94857

Hi All,
The following stored proc show 2 error messages below: What is wrong here?
Msg 102, Level 15, State 1, Procedure SP_ResetCreditLimit, Line 32
Incorrect syntax near 'UPDDATE'.
Msg 156, Level 15, State 1, Procedure SP_ResetCreditLimit, Line 90
Incorrect syntax near the keyword 'CLOSE'.
CREATE PROCEDURE [dbo].[SP_ResetCreditLimit]
AS
BEGIN TRY
DECLARE @Cust_Name VARCHAR(31), @Credit_Limit FLOAT, @TermDays INT, @OldValue VARCHAR(100)
BEGIN TRAN
DECLARE cur_Cust CURSOR FOR
Select Cust_Name, Credit_Limit, TermDays, OldValue
FROM Customer c
JOIN AuditLog al
ON al.NewValue = c.Credit_Limit
JOIN JobType jt
ON c.Cust_Type = jt.JobType
WHERE JobTypeFullName = 'CREDIT HOLD'
AND Credit_Limit <> .01
AND TermDays <> 1
AND TableName = 'CUSTOMER'
AND FieldName = 'Credit_Limit'
AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)
AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')
OPEN cur_Cust
FETCH NEXT FROM cur_Cust into @Cust_Name, @Credit_Limit, @TermDays, @OldValue
WHILE @@FETCH_STATUS=0
BEGIN
UPDDATE Customer
SET Credit_Limit = .01,
TermDays = 1
FROM Customer c
JOIN AuditLog al
ON al.KeyField1 = c.Cust_Name
JOIN JobType jt
ON c.Cust_Type = jt.JobType
WHERE JobTypeFullName = 'CREDIT HOLD'
AND Credit_Limit <> .01
AND TermDays <> 1
AND TableName = 'CUSTOMER'
AND FieldName = 'Credit_Limit'
AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)
AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')
--Insert Credit limit record
INSERT INTO AuditLog
(
TableName,
KeyField1,
FieldName,
OldValue,
NewValue,
UserName
)
VALUES
(
'CUSTOMER',
@Cust_Name,
'Credit_Limit',
@OldValue,
.01,
'SYSTEM-CREDITHOLD'
)
--Insert Term Days record
INSERT INTO AuditLog
(
TableName,
KeyField1,
FieldName,
OldValue,
NewValue,
UserName
)
VALUES
(
'CUSTOMER',
@Cust_Name,
'TermDays',
@OldValue,
1,
'SYSTEM-CREDITHOLD'
)
FETCH NEXT FROM cur_Cust into @Cust_Name, @Credit_Limit, @TermDays, @OldValue
END
CLOSE cur_Cust
DEALLOCATE cur_Cust
IF @@error > 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo
END CATCH

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

the error tells you exactly which line to go to to fix it....

it's pure syntax:

Msg 102, Level 15, State 1, Procedure SP_ResetCreditLimit, Line 32

Incorrect syntax near 'UPDDATE'.

Update is misspelled. remove the extra "D"

Msg 156, Level 15, State 1, Procedure SP_ResetCreditLimit, Line 90

Incorrect syntax near the keyword 'CLOSE'.

goes away automatically when the first error is corrected.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734449

December 22, 2008 at 2:14 pm

#916407

There's one "D" in update.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why are you using a cursor at all? The update doesn't reference the cursor row, meaning you're doing exactly the same update multiple times.

The two inserts can be done set-based without any problems:

INSERT INTO AuditLog (

TableName,

KeyField1,

FieldName,

OldValue,

NewValue,

UserName

)

Select 'CUSTOMER', Cust_Name, 'Credit_Limit', OldValue, .01, 'SYSTEM-CREDITHOLD'

FROM Customer c

JOIN AuditLog al

ON al.NewValue = c.Credit_Limit

JOIN JobType jt

ON c.Cust_Type = jt.JobType

WHERE JobTypeFullName = 'CREDIT HOLD'

AND Credit_Limit <> .01

AND TermDays <> 1

AND TableName = 'CUSTOMER'

AND FieldName = 'Credit_Limit'

AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)

AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')

Similar for the second.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramadesai108 SSChampion Points: 10609 More actions · Answer 4

Hello All,

I just could not see the extra "D" in the "UPDATE". Thanks for pointing that out. As far as "set based insert", I am little lost on that one. For each update, I need to make two entries in the AuditLog. One for credit limit and one for term days. Isn't it easier to use the 2 inserts based on the cursor values rather than doing a select as Gail pointed out? I am sure that Gail is correct but I need to understand.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

ramadesai108 (12/22/2008)
Isn't it easier to use the 2 inserts based on the cursor values rather than doing a select as Gail pointed out?

Easier? Maybe. Slower? Definitely.

Also that update is running once for each qualifying customer you have, but it's updating all of the customers each time. So if you have 100 qualifying customers, that update is updating all 100, 100 times. That's a lot of extra work.

There's absolutely no need for a cursor here at all. It's a waste of time and resources.

Basically, I can replace the entire contents of your Try block with this. Is the cursor easier?

UPDATE Customer

SET Credit_Limit = .01,

TermDays = 1

FROM Customer c

JOIN AuditLog al

ON al.KeyField1 = c.Cust_Name

JOIN JobType jt

ON c.Cust_Type = jt.JobType

WHERE JobTypeFullName = 'CREDIT HOLD'

AND Credit_Limit <> .01

AND TermDays <> 1

AND TableName = 'CUSTOMER'

AND FieldName = 'Credit_Limit'

AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)

AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')

INSERT INTO AuditLog (

TableName,

KeyField1,

FieldName,

OldValue,

NewValue,

UserName

)

Select 'CUSTOMER', Cust_Name, 'Credit_Limit', OldValue, .01, 'SYSTEM-CREDITHOLD'

FROM Customer c

JOIN AuditLog al

ON al.NewValue = c.Credit_Limit

JOIN JobType jt

ON c.Cust_Type = jt.JobType

WHERE JobTypeFullName = 'CREDIT HOLD'

AND Credit_Limit <> .01

AND TermDays <> 1

AND TableName = 'CUSTOMER'

AND FieldName = 'Credit_Limit'

AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)

AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')

INSERT INTO AuditLog (

TableName,

KeyField1,

FieldName,

OldValue,

NewValue,

UserName

)

Select 'CUSTOMER', Cust_Name, 'TermDays', TermDays, 1, 'SYSTEM-CREDITHOLD'

FROM Customer c

JOIN AuditLog al

ON al.NewValue = c.Credit_Limit

JOIN JobType jt

ON c.Cust_Type = jt.JobType

WHERE JobTypeFullName = 'CREDIT HOLD'

AND Credit_Limit <> .01

AND TermDays <> 1

AND TableName = 'CUSTOMER'

AND FieldName = 'Credit_Limit'

AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)

AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')

I think you also have a bug in the second update. You reference @OldValue, which contains the old credit value, while you're supposed to be auditing the old TermDays, which is in the variable @TermDays

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Thanks Gail,

I still have a cursor, but now I am updating based on the value from the cursor. Take a look at this tell me whether this is ok or not. I am learning from you.

CREATE PROCEDURE [dbo].[SP_ResetCreditLimit]

AS

BEGIN TRY

DECLARE @Cust_Name VARCHAR(31), @Credit_Limit FLOAT, @TermDays INT, @OldValue VARCHAR(100)

BEGIN TRAN

DECLARE cur_Cust CURSOR FOR

Select Cust_Name, Credit_Limit, TermDays, OldValue

FROM Customer c

JOIN AuditLog al

ON al.NewValue = c.Credit_Limit

JOIN JobType jt

ON c.Cust_Type = jt.JobType

WHERE JobTypeFullName = 'CREDIT HOLD'

AND Credit_Limit <> .01

AND TermDays <> 1

AND TableName = 'CUSTOMER'

AND FieldName = 'Credit_Limit'

AND CONVERT(VARCHAR(10),DateTime,101) = CONVERT(VARCHAR(10),'12/01/2008',101)

AND DATEPART(mi,DateTime) BETWEEN DATEPART(mi,'12/01/2008') AND DATEADD(mi,-60,'12/01/2008')

OPEN cur_Cust

FETCH NEXT FROM cur_Cust into @Cust_Name, @Credit_Limit, @TermDays, @OldValue

WHILE @@FETCH_STATUS=0

BEGIN

UPDATE Customer

SET Credit_Limit = .01,

TermDays = 1

WHERE Cust_Name = @Cust_Name

--Insert Credit limit record

INSERT INTO AuditLog

(

TableName,

KeyField1,

FieldName,

OldValue,

NewValue,

UserName

)

VALUES

(

'CUSTOMER',

@Cust_Name,

'Credit_Limit',

@OldValue,

.01,

'SYSTEM-CREDITHOLD'

)

--Insert Term Days record

INSERT INTO AuditLog

(

TableName,

KeyField1,

FieldName,

OldValue,

NewValue,

UserName

)

VALUES

(

'CUSTOMER',

@Cust_Name,

'TermDays',

@OldValue,

1,

'SYSTEM-CREDITHOLD'

)

FETCH NEXT FROM cur_Cust into @Cust_Name, @Credit_Limit, @TermDays, @OldValue

END

CLOSE cur_Cust

DEALLOCATE cur_Cust

IF @@error > 0

ROLLBACK TRAN

ELSE

COMMIT TRAN

END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXECUTE usp_GetErrorInfo

END CATCH

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

ramadesai108 (12/22/2008)
I still have a cursor

Why?

See my edited post above.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramadesai108 SSChampion Points: 10609 More actions · Answer 8

Thanks Gail. That makes perfect sense and it is taking less longer than the other one. Thanks again.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

It's an addiction, man, a crutch. Just give it up...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]