Variable not getting set by select

Question

I have an issue where a variable is not getting set by a select statement.

The select joins a table variable @contracts (which is used to control a loop) and then joins to the real contract and contract line tables.

my select is:

select top 1 
        @contract_id = c.contract_id 
        , @account = ch.account
        , @service = cl.service
        , @model = cl.model
        , @serial = cl.serial
        , @contract = ch.contract
    from 
        @contracts c
        inner join contractline cl on c.contract_id = cl.contract_id
        inner join contractheader ch on cl.contract = ch.contract
    where
        cl.contract_id = @contract_id

But when I do a select @contract_id say i get a NULL back, as do all my variables.

I have done a simple select * from @contracts c inner join contractline cl on c.contract_id = cl.contract_id inner join contractheader ch on cl.contract = ch.contract and this returns exactly 1 line with the values in all the correct places, no nulls in the selected values.

What have I done wrong?

Answer 1

Looks like your WHERE clause could be stopping the row from returning. You mentioned you've tested the same SELECT without setting variable, but the code you listed doesn't include this WHERE - so its not the same.

Test your SELECT with the WHERE in place, with dummy values (likely NULL) and review. It simply sounds like its not returning a result.

Answer 2

You are setting @contract_id in the select statement but then also using it in the where clause. I would have thought that @contract_id will be null?