Solved

MSSQL - Conditional Statements in Column Formula

Posted on 2002-03-12
3
1,212 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

829 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