CASE statement question

hi,

Suppose I want to do the following:

DECLARE @.num
SET @.num = -1 /* initial value */

SELECT col1, col2,
CASE @.num
WHEN -1 THEN ( [assign a value to @.num], [return that value] )
ELSE @.num
END AS 'num'
FROM T1

In English, I only want the @.num variable to be calculated once but appear in every column of the SELECT result, since table T1 has many rows.

Greatly appreciate any help... thanks in advance!!Hello,

it looks like SQL Server ?? Is it true ?

Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Yes... it is SQL Server...sql

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/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
>

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