Passing a variable in TSQL

Question

I have the code below and I want to loop through a table and set a variable that can then be used to updated a field in another table, but the variable keeps showing as NULL when I try to read it. What am I doing wrong?

declare @CustId nchar(15)
declare @CustID1 nchar(255)
declare @DateTime1 nchar(25)
declare @finalnote varchar(max)
declare @RowNum int

declare CustList cursor for
   select 
       DateTime, Username, Notes 
   from tbl_DesignNotes 
   where OrderNumber = 10645 
   order by ID

OPEN CustList

FETCH NEXT FROM CustList INTO @DateTime1, @CustId, @CustID1
set @RowNum = 0 

WHILE @@FETCH_STATUS = 0
BEGIN
    set @RowNum = @RowNum + 1 
    set @finalnote = @finalnote + ' ' + @DateTime1 + ' ' + @CustId + ' ' + @CustID1;
    --select @finalnote
    --print @finalnote
    select @finalnote as varText;

    --print @DateTime1 + ' ' + @CustId + ' ' + @CustID1
    FETCH NEXT FROM CustList INTO @DateTime1, @CustId, @CustID1
END

CLOSE CustList
DEALLOCATE CustList

There's a chance that \@DateTime1 or \@CustId or \@CustID1 is null so try using ISNULL(\@DateTime1, GETDATE()), etc. — Vincent Dagpin, Jul 22 '15 at 00:15
I think you can rewrite your code into a single statement, update bulk — Vincent Dagpin, Jul 22 '15 at 00:15
Have a look [here](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) for some methods of building a string from data in multiple rows without using an [RBAR](https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/) loop. — HABO, Jul 22 '15 at 03:48
**"that can then be used to updated a field in another table"** Take a look at the UPDATE command https://msdn.microsoft.com/en-us/library/ms177523.aspx you are allowed to use WITH and FROM either give you the ability to reference a field from some other table to be used in the update. Avoid cursors for simple operations such as an update. — Paul Maxwell, Jul 22 '15 at 04:57

Moe Sisko · Accepted Answer · 2015-07-22T23:57:56.650

@finalnote is not initialized, so it is NULL before it enters the loop. Concatenating anything with NULL will (usually*) result in NULL, so

set @finalnote = @finalnote + ' ' + @DateTime1 + ' ' + @CustId + ' ' + @CustID1;

will always result in NULL, regardless of what @DateTime1, @CustId and @CustID1 contain.

So try initializing it to an empty string before entering the loop. e.g.:

declare @finalnote varchar(max) = ''

Note *: for more, see: https://msdn.microsoft.com/en-us/library/ms176056%28v=sql.110%29.aspx

Passing a variable in TSQL

1 Answers1