Variable assignment in SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Variable assignment in SELECT

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2004-12-28 : 09:23:10

Ok, what type of issues should I consider when writing a statement such as:


DECLARE @A DECIMAL(24, 14)
DECLARE @B INT
DECLARE @C MONEY

SELECT
    @A = TBL.Cost,
    @B = <db.owner.functionname(1, 2, 3, 4),
    @C = ROUND(SUM(@A * @B), 2)
FROM MyTable AS TBL
GROUP BY <relevant values>

The situation I am encountering is that if I select @A, @B & @C after this type of statement @C is a NULL value when @A & @B have values.

rmhpirate
Starting Member

10 Posts

Posted - 2004-12-28 : 10:34:36

I would declare @C. Since you are declaring @A and @B, these values are set to null prior to assinging values to them. Without declaring @C in your declare statement, it's probably taking the @C in the SELECT statement and setting it as Null.

I'd try that to see what happens.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2004-12-28 : 10:51:07

Oops, my bad. I fixed the example. You can be sure that all the variables are declared.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 11:01:59

The value are not set one after the other but set using the initial values - @A and @B are null in the set of @C.
You have to do this in two statements or repeat the assignment.
Also you can only allocate a single value so the group by will not work.
i.e. you can only have a single TBL.Cost.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2004-12-28 : 11:16:54

quote:
Originally posted by nr

The value are not set one after the other but set using the initial values - @A and @B are null in the set of @C.
You have to do this in two statements or repeat the assignment.
Also you can only allocate a single value so the group by will not work.
i.e. you can only have a single TBL.Cost.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bah. So I can assign variables to a singleton result. But if I force the underlying data to "look" like one row it won't work. Bummer. Oh well, I'll just toss the values into a temp table and summarize there. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources