Case conditional action

Is it possible to check a variable in a case statement then perform a select
query depending on the variable? What I have tried is something like this
which does not work. aNyone know how this can be accomplished?
declare @.@.var int, @.rtn int
set @.var = 3
select case @.var when 1 select @.rtn = catid from categories
when 2 select @.rtn = itmid from categories
when 3 select @.rtn = catmid from categorymixbjamin wrote:
> Is it possible to check a variable in a case statement then perform a sele
ct
> query depending on the variable? What I have tried is something like this
> which does not work. aNyone know how this can be accomplished?
>
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
A CASE expression is like this:
CASE <condition>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
.. etc. ...
END
Use IF:
IF @.var = 1
SELECT @.rtn = catid FROM categories
IF @.var = 2
SELECT @.rtn = itmid FROM categories
IF @.var = 3
SELECT @.rtn = catmid FROM categorymix
These statements assume that there is only 1 row in each table, which is
probably wrong. What are you really trying to do?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAy4TYechKqOuFEgEQKYbwCgzax4m6K6c5lD
CcRV3sxStCYnhlUAoPRg
pAwpcd7fD1BWfkIGeB21eKuG
=opZI
--END PGP SIGNATURE--|||bjamin (bjamin@.discussions.microsoft.com) writes:
> Is it possible to check a variable in a case statement then perform a
> select query depending on the variable? What I have tried is something
> like this which does not work. aNyone know how this can be
> accomplished?
There isn't any CASE statement in SQL. There is a CASE expression, which is
something different.

> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
Could write:
SELECT @.rtn = CASE WHEN 1 THEN (SELECT catid FROM categories)
WHEN 2 THEN (SELECT itmid FROM categories)
WHEN 3 THEN (SELECT catmid FROM categorymix)
EMD
Although, I think most people would prefer to use IF/ELSE in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cascade set to null on delete.

Hi all,

I am using SQL Server 2000 and am trying to perform a basic delete query on a table called ClientType. The only child table of this is called Client. The relationship between the tables has a cascade action of cascade update and when I try to perform the delete operation, I get the error "DELETE statement conflicted with COLUMN REFERENCE constraint". The foreign key field accepts nulls and has a defauilt value of null. Now, am I being completely dense or shouldn't the cascade update set the foreign key values to null?

Regards,

Stephen.

Could you please send over the DDL for the table and the constraints, thanks.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||

Here you go:
...

CREATE TABLE [dbo].[ClientType] (
[ClientTypeId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[Type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Client] (
[ClientId] [int] IDENTITY (1, 1) NOT NULL ,
[ObjectVersion] [int] NULL ,
[ClientTypeId] [int] NULL ,
[ReferenceNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forename] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Initial] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [datetime] NULL ,
[DateRegistered] [datetime] NULL ,
[Address1] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address3] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Postcode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Telephone3] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Position] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Overview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InternalOverview] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CV] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HoldingCompanyName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] WITH NOCHECK ADD
CONSTRAINT [PK_ClientType] PRIMARY KEY CLUSTERED
(
[ClientTypeId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientId]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ClientType] ADD
CONSTRAINT [DF_ClientType_ObjectVersion] DEFAULT (0) FOR [ObjectVersion]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [DF_Client_ObjectVersion] DEFAULT (0) FOR [ObjectVersion],
CONSTRAINT [DF_Client_ClientTypeId] DEFAULT (null) FOR [ClientTypeId]
GO

ALTER TABLE [dbo].[Client] ADD
CONSTRAINT [FK_Client_ClientType] FOREIGN KEY
(
[ClientTypeId]
) REFERENCES [dbo].[ClientType] (
[ClientTypeId]
) ON UPDATE CASCADE
GO

...

Regards,

Stephen.

|||OK, you just defined the Cascade on the update, if you delete a row and the child table contains rows for that parent row and you did not define a cascade delete on the parent table, this error message will come up.

You wil have to add: ON DELETE CASCADE

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for the response. I do understand that adding cascade delete would get rid of the error but will that not result in the child rows being deleted? I only want the foreign keys to be set to null, not have the entire related record dropped from the table.

Regards,

Stephen.

|||OK, then you will have to use the ON DELETE SET NULL.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de|||OK. I gave that a go, but I just get an error "incorrect syntax near the keyword 'SET'".|||

Sorry, I re-read your first post and saw that you are using SQL Server 2k. SET NULL is a new feature for SQL 2k5. In SQL Server 2000 you probably would use no constraint in that case and do the work with triggers, in that case an update trigger.

Sorry for confusing you :-)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||

OK. I'll look into that.

Thanks for your help. Its much appreciated.

Regards,

Stephen.