using CASE WHEN statement within an INSERT statement

[jayy66]

I was wondering if anybody could help me. My boss wants me to get rid of my If Else statements and use a CASE WHEN statement to use a single Insert Statement to insert data into a table.
This is the first time I've attempted to use a CASE expression within a INSERT statement.
Here is my code:

INSERT INTO tblDBChanges  (PatientVisitID, Reason, logText, created, createdBy) 
			CASE @tempLock 
				When 1 Then Values (@tempPVID, 'Lock Release (Letters)',@letterName + ' was locked on: Faxed', getdate(), @user)
				When 4 Then Values (@tempPVID, 'Lock Release (Letters)',@letterName + ' was locked on: Printed or Printed to Queue', getdate(), @user)
				When 8 Then Values (@tempPVID, 'Lock Release (Letters)',@letterName + ' was locked on: E-mailed', getdate(), @user)
				When 10 Then Values (@tempPVID, 'Lock Release (Letters)',@letterName + ' was locked on: Whiteboard viewed', getdate(), @user)
			End

I keep getting a "Syntax error near the word 'CASE'" error.
Does anybody see he error of my ways??

Thanks in advance

 

[AlexCuse]

Try this

CASE WHEN @TEMPLOCK = 1 THEN...
WHEN @TEMPLOCK = 2 THEN...


and so on

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[djj55]

Try something like this:

INSERT INTO tblDBChanges  (PatientVisitID, Reason, logText, created, createdBy) 
	select @tempPVID, 'Lock Release (Letters)', Text =
            CASE @tempLock 
                When 1 Then @letterName + ' was locked on: Faxed'
                When 4 Then @letterName + ' was locked on: Printed or Printed to Queue'
                When 8 Then @letterName + ' was locked on: E-mailed'
                When 10 Then @letterName + ' was locked on: Whiteboard viewed'
            End   , getdate(), @user)

This is a quick look, I have not used CASE resently.
djj

 

[gmmastros]

Untested....

INSERT INTO tblDBChanges  (PatientVisitID, Reason, logText, created, createdBy) 
Values (
       @tempPVID, 
       'Lock Release (Letters)',
       @letterName 
       + Case @TempLock 
              When 1  Then ' was locked on: Faxed'
              When 4  Then ' was locked on: Printed or Printed to Queue'
              When 8  Then ' was locked on: E-mailed'
              When 10 Then ' was locked on: Whiteboard viewed'
         End, 
       getdate(), 
       @user)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jayy66]

Thanks everyone for the amazingly quick responses. I had tried all your suggestions before with no luck except for gmmastros suggestion and that worked to perfection.

Thanks again for everyones input.

 

[AlexCuse]

Ah, I didn't realize you could format a CASE Statement that way. Of course mine always seem to be handling nulls so I can't, but it is good to know anyway. Thanks George, have a *

Alex




It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[gmmastros]

Ah.... speaking of nulls... When using case, it's best to accomodate the nulls, which the original code didn't and my modified version didn't either. The case statement is 'controlled' by @TempLock. If @TempLock is NULL or any value other than 1, 4, 8, or 10, then the case part will reutrn null. Null added to another string will be NULL, so the data inserted will be NULL.

Instead, I'd like to offer this slight modification. You may think... I completely control the @TempLock variable, but it's best to get in to the habit of accomodating 'other' values by using Else within the case statement, like this...

INSERT INTO tblDBChanges  (PatientVisitID, Reason, logText, created, createdBy) 
Values (
       @tempPVID, 
       'Lock Release (Letters)',
       @letterName 
       + Case @TempLock 
              When 1  Then ' was locked on: Faxed'
              When 4  Then ' was locked on: Printed or Printed to Queue'
              When 8  Then ' was locked on: E-mailed'
              When 10 Then ' was locked on: Whiteboard viewed'
              [!]Else ''[/!]
         End, 
       getdate(), 
       @user)

So... If @TempLock isn't handled by the case statement, an empty string will be added to @lettername instead of causing it to be NULL.

Thanks Alex for bringing up the NULL issue.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom