Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Case within an Insert Statement Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 8:27 AM
Points: 4, Visits: 15
I have an Insert statement. One of the columns to be added will be either 'Y' or 'N' depending on the value of another queried column.

Pseudocode would be something like this, where I need to insert into field 'valid' based on the value of description.

Insert into table
values (@ssn,@empid,@race,
CASE @description
WHEN 'deputy' THEN valid = 'Y'
WHEN 'civilian' THEN valid ='N'
end
)

Thanks.
Post #1046095
Posted Tuesday, January 11, 2011 1:15 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, April 13, 2014 8:33 PM
Points: 456, Visits: 415
You should be able to write it with the case statement as a field. So it would look like this:

insert into table
values (@ssn,@empid,@race,
CASE @description
WHEN 'deputy' THEN 'Y'
WHEN 'civilian' THEN 'N'
end
)

The case itself is the field; so you do not have to set a value to return.

I hope this answers your question.
Post #1046119
Posted Tuesday, January 11, 2011 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 8:27 AM
Points: 4, Visits: 15
The name of the field to be updated is different than the field which contains the determining value.

I need to update field 'valid' based on the value in field 'description'.
Post #1046157
Posted Tuesday, January 11, 2011 2:17 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, April 13, 2014 8:33 PM
Points: 456, Visits: 415
If you need to include description and the valid column you would just need to add it into values list:

insert into table (ssn,empid,race,description,valid)
values (
@ssn,
@empid,
@race,
@description,
CASE @description
WHEN 'deputy' THEN 'Y'
WHEN 'civilian' THEN 'N'
end
)

It shouldn't hurt anything to do that. Valid is a column in the same table right?

Hope to help,
Dane
Post #1046160
Posted Tuesday, January 11, 2011 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 8:27 AM
Points: 4, Visits: 15
If I have values after the case statement what would it look like?

I'm not sure of the syntax. Also I don't have the columns hardcoded on the insert line because I'm putting data into all of them.

insert into table
values (
@ssn,
@empid,
@race,
@description,
CASE @description
WHEN 'deputy' THEN 'Y'
WHEN 'civilian' THEN 'N'
end,
column1,
column2,

)
Post #1046168
Posted Tuesday, January 11, 2011 2:46 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, April 13, 2014 8:33 PM
Points: 456, Visits: 415
How you typed it seems correct. I am assuming that the trailing comma is a typo. There are hidden dangers with not specifying the columns you are inserting. If the table ever changes your insert will definitely fail unless it is also touched.

Hope this helps,
Dane
Post #1046171
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse