Check this out, the first post as 2 possible solutions. I'd go for the 2nd one if this is gonna run more than once.

Extended challenge for everyone -...

No, the testcase was just toshow you how to make it work in general. The solution must be adapted to your tables.

And this one (check out the discussion after the article... he pretty much gets destroyed all across the board).

Database Design and Reference Tables

You owe her diner .

Lookup table madness

ALTER PROCEDURE procJoinColuna

@coluna varchar( 2000 ),

@valor varchar( 2000 ),

@delimiter char( 1 ),

@saida varchar( 2000 ) OUTPUT

AS

SET @saida = ''

DECLARE @cr_rolename varchar( 2000 )

DECLARE @cr_sql varchar( 2000 )

...the code...

SET @saida =...

Isn't that harder to handle than a null column?

Please post the table definition, sample data and the required reaction for each operation.

The table HAS TO be created in tempdb has it can theorically hold more data than available memory. So the server must have a way to write the data...

I stopped counting the recuring questions... I just saved the answers and paste them back .

Doh, missed the inversion of the table names... but you stil have to move the outer table conditions from the where part to the join part or it'll fail.

Ever considered that the previous statement is the one giving out wrong results?

Or are we not seeing the whole picture?

SELECT t2.data,t2.id,t1.data

from table1 t1 left outer join table2 t2

on t1.id=t2.id and t2.data=2 and t1.data=5

What are you trying to do exactly starting from my boss wants me to do this?

Look up the case statement, you don't need dynamic sql for this.

The Curse and Blessings of Dynamic SQL

You can only use EXEC in a function if calling and extended procedure.

Why do you think you need dynamic sql for this task?