Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)