Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1
>
Showing posts with label casestatement. Show all posts
Showing posts with label casestatement. Show all posts
Tuesday, March 27, 2012
CASE Statement
Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10
You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>
|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10
You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>
|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
CASE Statement
Hello,
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am trying to understand how to use CASE statements within a CASE
statement. I am using the following but getting the error:
Incorrect syntax near the keyword 'CASE'.
declare @.MyDate DATETIME, @.YearType varchar(255)
set @.MyDate = getdate()
set @.YearType = 'fiscal'
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
Convert(varchar,year(@.MyDate))
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
Convert(varchar,year(@.MyDate))
END
Any help would be appreciated.
Thanks in advance,
sck10You are missing END in the inner CASEs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sck10" <sck10@.online.nospam> wrote in message news:upANGW6XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I am trying to understand how to use CASE statements within a CASE
> statement. I am using the following but getting the error:
> Incorrect syntax near the keyword 'CASE'.
> declare @.MyDate DATETIME, @.YearType varchar(255)
> set @.MyDate = getdate()
> set @.YearType = 'fiscal'
> Select
> CASE @.YearType
> WHEN 'fiscal' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate) - 1)
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04' +
> Convert(varchar,year(@.MyDate))
> WHEN 'calendar' THEN
> CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
> Convert(varchar,year(@.MyDate) )
> CASE WHEN month(@.MyDate) BETWEEN 10 AND 12 THEN 'Q04-' +
> Convert(varchar,year(@.MyDate))
> END
>
> Any help would be appreciated.
> --
> Thanks in advance,
> sck10
>|||On Mon, 23 May 2005 09:41:35 -0500, sck10 wrote:
>Hello,
>I am trying to understand how to use CASE statements within a CASE
>statement. I am using the following but getting the error:
>Incorrect syntax near the keyword 'CASE'.
Hi sck10,
Tibor already found the missing END's, but he seems to have missed the
extraneous CASE's.
Select
CASE @.YearType
WHEN 'fiscal' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) - 1)
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q04-' +
Convert(varchar,year(@.MyDate) - 1)
ELSE 'Q04' + Convert(varchar,year(@.MyDate))
END
WHEN 'calendar' THEN
CASE WHEN month(@.MyDate) BETWEEN 1 AND 3 THEN 'Q01-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 4 AND 6 THEN 'Q02-' +
Convert(varchar,year(@.MyDate) )
WHEN month(@.MyDate) BETWEEN 7 AND 9 THEN 'Q03-' +
Convert(varchar,year(@.MyDate) )
ELSE 'Q04-' + Convert(varchar,year(@.MyDate))
END
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Case statement
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1
>sql
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1
>sql
Subscribe to:
Posts (Atom)