Update does not work in stored procedure

0 pts.
Tags:
Microsoft Windows
SQL
I have an update statement that works fine straight up, but fails when included in a stored procedure. Can you help? SET Deny_Cd = (SELECT Deny_Code FROM CPSA_CIS_Edit_Processing WHERE claim = x.Claim AND Edit_Code = (SELECT MIN(SUBSTRING(Edit_Code,1,1)+Convert(char(3),CONVERT(INTEGER,SUBSTRING(Edit_Code,2,LEN(Edit_Code)-1)))) FROM CPSA_CIS_Edit_Processing WHERE claim = x.claim AND deny_code != null)), Checkk_Date = getdate(), Change_Date = getdate(), Change_Init = 'CISEdtTx' FROM CPSA_CIS_Edit_Processing x WHERE Med_Claims.Claim = x.Claim AND x.Process_Action = 'Deny' AND x.Deny_Code != null Table data is: Claim Episode Edit_Code Edit_Action Deny_Code Process_Action 05207E20K4 F78N51 F78 Deny D490 05207E20K4 F78N51 N51 Deny D051 Deny 05226E90S7 N51F75 F75 Deny D480 05226E90S7 N51F75 N51 Deny D051 Deny 05226E93EG N53F77 F77 Deny D491 05226E93EG N53F77 N53 Deny D430 Deny 05226EC146 F26N30 F26 None [NULL] 05226EC146 F26N30 N30 Deny D Deny 05226EC1C5 N63N207 N207 Deny D207 05226EC1C5 N63N207 N63 Deny D420 Deny 05226EC2FU N63F83 F83 Deny D 05226EC2FU N63F83 N63 Deny D420 Deny Also tried to use a case statement for the set and had the same problem. Thanks.
ASKED: February 24, 2006  3:08 PM
UPDATED: February 27, 2006  11:11 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You may have the commitment level set differently. Very often, running SQL statements separately, the commitment level is set to AutoCommit or *NONE. Typically, programs have easiest commitment, or *CHG. Try adding EXEC SQL COMMIT to your program.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Stephen09
    What is the error message (if any) that you are getting? That would help us get a better idea of the problem.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following