?
Solved

MSSQL - Conditional Statements in Column Formula

Posted on 2002-03-12
3
Medium Priority
?
1,218 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

764 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