• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Problem with sql case statement

Can someone show me the proper way to use the case statement in this example?

EXEC MedscribeSQL.dbo.usp_PatientDemographicsUpdate @FName, @MName, @LName, @DOB, @SSNum, @SEX, @HPhone,
                                                                                                CASE      
                                                                                                      WHEN @CreditCode = null THEN @OriginalCreditCode
                                                                                                      ELSE @CreditCode
                                                                                                END as CreditCode,
                                                                    @Org, @ChartNum, @Dec, @Addr1, @Addr2, @City, @State, @Zip, @Race, 1,
                                                                    @EthnicityId,@SBLocationId,@PhysId,0,null,'ROBOT1',null,1,
                                                                                                @BillType,@AccountNo,@Alert;


For creditcode I'm basically wanting to use the originalvalue if the new value is null.

The editor says incorrect syntax near CASE   and same for AS at the end of the CASE statement.

Is there a better way to do this perhaps?
0
rutledgj
Asked:
rutledgj
  • 2
2 Solutions
 
malikirfan28Commented:
Why do you not place this check before the EXEC statement like this

if @CreditCode = null
begin
   @CreditCode = @OriginalCreditCode
end
 and then call simply

EXEC MedscribeSQL.dbo.usp_PatientDemographicsUpdate

So your complete code will be

if @CreditCode = null 
begin
   @CreditCode = @OriginalCreditCode
end

EXEC MedscribeSQL.dbo.usp_PatientDemographicsUpdate @FName, @MName, @LName, @DOB, @SSNum, @SEX, @HPhone, @CreditCode,
                                                                    @Org, @ChartNum, @Dec, @Addr1, @Addr2, @City, @State, @Zip, @Race, 1,
                                                                    @EthnicityId,@SBLocationId,@PhysId,0,null,'ROBOT1',null,1,
                                                                                                @BillType,@AccountNo,@Alert;

Open in new window

0
 
knightEknightCommented:
You will need to do the CASE first, then pass in the result as a parameter:

declare @parmCreditCode varchar(80)
select  @parmCreditCode = CASE    
                                               WHEN @CreditCode = null THEN @OriginalCreditCode
                                               ELSE @CreditCode
                                       END --as CreditCode,

EXEC MedscribeSQL.dbo.usp_PatientDemographicsUpdate @FName, @MName, @LName, @DOB, @SSNum, @SEX, @HPhone,
        @parmCreditCode,
        @Org, @ChartNum, @Dec, @Addr1, @Addr2, @City, @State, @Zip, @Race, 1,
        @EthnicityId,@SBLocationId,@PhysId,0,null,'ROBOT1',null,1,
        @BillType,@AccountNo,@Alert;
0
 
knightEknightCommented:
In this case, easier would be:

select  @parmCreditCode = isNull(@CreditCode,@OriginalCreditCode)

EXEC MedscribeSQL.dbo.usp_PatientDemographicsUpdate @FName, @MName, @LName, @DOB, @SSNum, @SEX, @HPhone,
        @parmCreditCode,
        @Org, @ChartNum, @Dec, @Addr1, @Addr2, @City, @State, @Zip, @Race, 1,
        @EthnicityId,@SBLocationId,@PhysId,0,null,'ROBOT1',null,1,
        @BillType,@AccountNo,@Alert;
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now