Temp table weirdness, pls help

[iamtechuser]

Hi,

I am running the following stmts.

insert into #tmp EXEC @result = master..xp_cmdshell @cmd

SELECT @output = IsNull(@output + char(10) + result, result)
FROM #tmp

PRINT @output -- this is NULL!!

SELECT * from #tmp -- this returns ROWS!!

END

I don't understand this... I believe it has something to do w/ the fact that I am select from a temp table, although it shouldn't. But if I select a regular table, then @output is NOT null, so it seems very suspicious it's the temp table.

What gives??

Thanks

 

[cmartin75]

I can only figure out that the result field in the last row of the temp table will be a null value, then the IsNull function will return that value.

It's possible?

 

[SQLSister]

If you will ever have more than one row in #tmp you cannot set the value to @output in a select statment and have good results even if it does have a value. Which record's value did you want? The first one, the tenth one. If you are setting a variable value ina SELECT statment, the select must be such that you can only ever have one value.

IT might be easier to suggest how to do what you want with some more details about what is in #temp, what type of datatype@output is and what the value of @output is before you runthe select (is it currently null? in which case your whole isnull statement is useless as you will 100% of the time return result (Nulls concatenated to any other data render the result as null.)

"NOTHING is more important in a database than integrity." ESquared

 

[iamtechuser]

I'm sorry that isn't true... the problem is not multiple rows in the table not being able to be stored in the variable... it's the TEMP table

I can do the select into the variable just fine with a regular table, and the @output formats the multiple rows beautifully... that what this is doing:

SELECT @output = IsNull(@output + char(10) + result, result)
FROM whateverTable

Try it... you'll see that it works great for formatting multiple rows in any table, so long as it's NOT a temp table.

My question is about - why doesn't the TEMP table work...

Thanks

 

[iamtechuser]

Although you do have a point about NULLs in the table... that does mess things up.

 

[iamtechuser]

So, here is the workaround:

create table myTable(name varchar(20))
insert into myTable values ('Sue')
insert into myTable values ('Joe')
insert into myTable values (null)
insert into myTable values ('Meg')
insert into myTable values ('Ryan')

DECLARE @myVar VARCHAR(100)

SELECT
@myVar = IsNull(@MyVar + char(10) + name, name)
FROM myTable where name is not null

PRINT @myVar

 

[iamtechuser]

Duh, the WHERE IS NOT NULL clause is what I needed!!

Now works great!!