Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSSQL - Conditional Statements in Column Formula

Posted on 2002-03-12
3
Medium Priority
?
1,219 Views
Last Modified: 2007-12-19
Is it possible to have conditional statements in the column formula (when designing a table)? What is the correct syntax?
0
Comment
Question by:cdzuniga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 6857374
Yes, it is, but not by using IF.  Instead, use CASE, e.g.:

SELECT MyField1, (Case MyGender When 'M' Then 'Male' When 'F' Then 'Female' Else '???' END) As Gender
FROM MyTable
0
 
LVL 5

Accepted Solution

by:
kelfink earned 300 total points
ID: 6857889
CHECK constraints sound like what you want...
To guarantee that a salary is less than 20 or greater than 20000, use:

create table strangesalary
( salary int check ( salary < 20 or salary > 20000));

What you can't do is have one column reference others in an expression:

create table failure
( hourly int check ( hourly is not null or monthly is not null),
  monthly int );
gives error:
Server: Msg 8141, Level 16, State 1, Line 1
Column CHECK constraint for column 'hourly' references another column, table 'failure'.

You can enforce a set of values with:

create table person
( gender char(1) check ( gender in('M','F') ) ) ;
0
 

Expert Comment

by:cafeoui
ID: 14941910
The original solution does not address the reason for the error message.

The reason for the original error message is that, with SQL Server, column CONSTRAINTS cannot reference other columns.  To apply a constraint referencing multiple columns, the CONSTRAINT must be a table constraint.

WRONG:
create table failure
( hourly int check ( hourly is not null or monthly is not null),
  monthly int );

RIGHT:
create table failure
( hourly int ,
  monthly int,
CONSTRAINT cck_f_hm CHECK ( hourly IS NOT null OR monthly IS NOT null));

Note that the constraint follows the last comma; thus, separating it from any single column...that makes it a table constraint.  This will result in a successful CREATE TABLE with the desired constraint.

Also, note that labeling the constraint with "cck_f_hm" is not required but I always name my constraints.  You never know when you may need to reference that constraint.  It's that "...ounce of prevention..." thing...  ;-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question