Ask the Expert

Error adding a stored procedure to UPDATE

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

I also tried to use a case statement for the set and had the same problem.

    Requires Free Membership to View

There are several issues with this. The most basic is that you can't test if something is NULL by saying thing != null NULL is the lack of a value, not a value that you can use in this sort of test. For example NULL is not equal to NULL ! Because of this, the SQL language offers another way to test for it. You need to check it by:

Thing IS NOT NULL

View questions and answers from all of our SQL Server experts here.

This was first published in March 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

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
Sort by: OldestNewest

Forgot Password?

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

Your password has been sent to: