Solved

MSSQL - Conditional Statements in Column Formula

Posted on 2002-03-12
3
1,209 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
3 Comments
 
LVL 32

Expert Comment

by:bhess1
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 100 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now