I am not that experienced in SQL 2005 and I'm trying to debug a stored proc (written by a far more experienced programmer which is why I'm appending here.)
At the top of the proc is the statement 'DECLARE @BillActualRoom bit'. If I understand correctly this is a local variable.
Later in the code we have the statement
'SELECT
@BillActualRoom = 0 FROM BillingOptions WHERE CenterID = @CenterID'where @CenterID is an input parameter.
The table BillingOptions does have a field called 'BillActualRoom' and it is a bit.
My question is this: Does this SQL statement make any sense at all (My gut reaction is no but I'd like a second opinion. Can't get any consensus in the office.)
I would think that putting a local variable in like this would mean that @BillActualRoom is always equal to 0.
I need a second opinion on SQL 2005 syntax
mikecoop83
Carlton Lane
Ok, so no, it "probably" doesn't make any sense, but... the value of @BillActualRoom will not always be 0.
If the value of @CenterID is not found in the table, the value of the @BillActualRoom variable will not be affected:
CREATE TABLE BillingOptions
(
BillingOptionsId int primary key,
CenterId int not null,
BillActualRoom bit not null
)
INSERT INTO BillingOptions
values (1,1,1)
DECLARE @BillActualRoom bit
SELECT @BillActualRoom = 0
FROM BillingOptions WHERE CenterID = 1
SELECT @BillActualRoom --returns 0, since the values is in the table
--then set the value to 1 (null works too)
SELECT @BillActualRoom = 1
SELECT @BillActualRoom = 0
FROM BillingOptions WHERE CenterID = 2
SELECT @BillActualRoom --this returns 1
This is really one of the more tricky parts of using the @Variable = syntax in select statements. You really have to be careful to clear out variables that you are reusing.
HackMax
Hello
The above statement makes "no sense" ... You will fetch a row that will return the data from the billing options table, but instead of using the data you just retrieved you store a "0" in your variable. It wont matter what the option in the option table is... You will always retrieve the constant
SELECT @BillActualRoom = BillActualRoom FROM BillingOptions WHERE CenterID = @CenterID
would be correct.
Ramon A. Paulino
Yeah, no doubt. I just wanted to make it clear that the code was not 100% guaranteed to return the same value either way.