Problem with CASE stmt

Question

Post reply

Problem with CASE stmt

cdun2

SSCertifiable

Points: 7893
More actions
July 12, 2005 at 10:20 am

#68158

Hello,
I am trying to use CASE in a SELECT list that provides data for an INSERT. I keep getting a syntax error on a line in the SELECT stmt where the CASE stmt is (BDate). The following is the code:
*****************************
CREATE PROCEDURE dbo.st_POS_INSERT_ClientInfoCkTobaccoCode
@EntrCode NVARCHAR(3),--required
@AppId INTEGER, --required
@ClientId INTEGER, --required
@ClientType NVARCHAR(1),
@FirstName NVARCHAR(20),
@MidName NVARCHAR(20),
@LastName NVARCHAR(30),
@BDate DATETIME,
@BirthState NVARCHAR(3),
@BirthCountry NVARCHAR(10)
AS
DECLARE @CheckBDate INTEGER, @NullBDate INTEGER
SET @CheckBDate = CAST(@BDate AS INTEGER)
SET @NullBDate = NULL
INSERT INTO snbat011_app_clnt
     (entr_cd,
      app_id,
      clnt_id,
      clnt_type,
      clnt_frst_name,
      clnt_mid_name,
      clnt_lst_name,
      clnt_dob,
      clnt_brth_st,
      clnt_brth_cntry
     &nbsp

SELECT
@EntrCode,
@AppId,
@ClientId,
'', -- client type
@FirstName,
@MidName,
@LastName,
BDate = CASE @CheckBDate WHEN = 0 THEN @NullBDate ELSE @BDate END,
@BirthState,
@BirthCountry
**********************************
Can I not use a CASE statement this way?
Thanks!
CSDunn

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

July 12, 2005 at 10:33 am

#573533

Is it a casting error?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

CASE @CheckBDate WHEN 0 THEN @NullBDate ELSE @BDate END AS BDate,

noeld SSC Guru Points: 96590 More actions · Answer 3

you should use the searched way instead:

CASE WHEN @CheckBDate = 0 THEN @NullBDate ELSE @BDate END

because the types of the expresssions are not the same

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 4

No,

What I meant is that if you are going to use the way that the poster issued all expressions involved should either be of the same type or implicitely converrtible otherwise you have to use the searched version of case which is what we posted

* Noel

cdun2 SSCertifiable Points: 7893 More actions · Answer 5

cdun2

SSCertifiable

Points: 7893

July 12, 2005 at 12:11 pm

#573604

Thank you for your help!

CSDunn

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

What was the problem (so that new readers know what to try)?

noeld SSC Guru Points: 96590 More actions · Answer 7

>> What was the problem (so that new readers know what to try)? <<

I think you are reading too fast ( or not ?) today

He was getting a syntax error (Do to the implicit conversion issue... read my post )

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

There was 2 slightly different solutions posted... maybe one of them didn't work for him (however not likely). Not everyone here is a seasoned DBA Noeld .

noeld SSC Guru Points: 96590 More actions · Answer 9

I must admit that it was me who didn't read your post carefully

CASE @CheckBDate WHEN = 0 THEN @NullBDate ELSE @BDate END AS BDate

should have been

CASE @CheckBDate WHEN 0 THEN @NullBDate ELSE @BDate END AS BDate

no "="

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Now that's 3 solution. Only 2 workings though . I should stop taking those for granted and check the spelling anyways... always the small things that byte you in the @$$.