Q

Error adding a stored procedure to UPDATE

SQL Server Development expert Greg Low analyzes an error message received when including a stored procedure in an UPDATE statement.

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.

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

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close