Always a SET-based solution in T-SQL?

[Brian]

Is there a set-based approach to building a dynamic SQL
string?

I've been asked to create a string of values base on the
condition that the record has been flagged deleted (soft
delete in our system.)

<-- The code pasted at the bottom -->

I'm dumping the PK values for the "deleted" records to a
temp table and running a WHILE loop to dynamically create
a string comprised of the PK values.

I'd love to know if it is possible to do this without a
WHILE loop (or god-forbid a CURSOR) to create the string
of values.

There must be a set-based approach to turn "vertical" data
into a "horizontal" string. Does anyone know the
technique?

Thanks,

-- Brian

Declare @Cnt int,
@Counter int,
@SQLString varchar(2000),
@PKField int

CREATE TABLE #MCStaging(

TableID int NOT NULL IDENTITY, -- need IDENTITY field
PersonID in NOT NULL

)

INSERT #MCStaging
SELECT PersonID from Person where DeleteFlag = 1

SET @Cnt = @@ROWCOUNT

SET @Counter = 1
SET @SQLString = ''

WHILE @Counter <= @Cnt

BEGIN
SELECT @PKField = PersonID from #MCStaging
WHERE TableID = @Counter

SELECT @SQLString = @SQLString +
cast(@PKField as varchar(20))

SET @Counter = @Counter+1
END

-- PRINT @SQLString

[Alex B]

Yes, but there must be a way around this because turning "vertical" data to
horizontal seems, well, unwise (i realize you may be stuck working with
other programmers who you might not be able to convince otherwise)...Why do
you need a string of those? Is there another means of handling what you
want to do? Among the many reasons why it's a questionable is that it's not
a very scalable approach - what happens when a varchar won't handle all of
the deleted PK values?

But, for your answer, I tried this and it works fine...

declare @fred varchar(4000)
set @fred=''
select @fred=@fred+yourPKcode from yourPKcodetable
select @fred

"Brian" <brian.l...@axean.com> wrote in message
news:394d01c1477f$86fe3570$a5e62ecf@tkmsftngxa07...

[brian linebaugh]

I never knew that SELECT could work like that...
that's works well for now. Thanks.

But yes, this is not something we should do. The developers wanted a string to work with rather than a table of values...I will try again to make the change.

Thanks,

-- Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Umachandar Jayachandran]

Don't use that technique! It will work only on some tables, for some
queries & for some set of data. Use a cursor loop or a static SQL using
GROUP BY to build the strings. If you search this newsgroup, I have posted
several samples where that code breaks easily. So don't use such techniques
that rely on particular query plan.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

[Umachandar Jayachandran]

>> select @fred=@fred+yourPKcode from yourPKcodetable

It is risky to use that technique! There is no guarantee that it will
work in all cases. It is best to write code that doesn't exploit any
behavior in the optimizer. If you search this newsgroup, I have posted

several samples where that code breaks easily.

--

[Alex B]

I agree that it's risky. I haven't been able to find any way to break this
particular example, (I totally believe there are ways), but it definitely
feels like a "hack" way, and therefore a poor long-term plan...

"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:e#lA1X5RBHA.1372@tkmsftngp03...

[brian linebaugh]

Thank you Mr. Jayachandran for the response and warning.

You mention using static SQL and GROUP BY to build the string. Do you have a small example, please?

I'm really interested in a non-cursor, non-row-processing set-based solutions to these problems.

Thanks again,

-- Brian

(Unkalukku Tamil teriyuma?
Naan anji varushama maduraiyil padichen.
Arumaiyaana mozhi, saar!)

[Umachandar Jayachandran]

Here are some examples that show why it is risky to use. I posted this
else where before. Try to analyze each one!

-- #1
declare @x varchar( 8000 ), @i int
set @x = ''
select @x = @x + au_id + ','
from pubs..authors
order by au_id + ','
print @x

-- #2
set @x = ''
set @i = 0
select @x = @x + a1.au_id + ',',
@i = @i + count(*)
from pubs..authors as a1
join pubs..authors as a2
on a2.au_id <= a1.au_id
group by @x + a1.au_id + ','
print @x
print @i

-- #3
set @x = ''
select distinct @x = @x + table_name + ','
from information_schema.columns
print @x

-- #4
declare @y varchar( 8000 )
set @x = ''
set @y = ''
select @x = @x + au_id + ',' ,
@y = @y + left( @x , 1 ) + ','
from pubs..authors
print @x
print @y

[Umachandar Jayachandran]

One way is to use the T-SQL UPDATE extension:

DECLARE @SQLString varchar( 8000 )
SET @SQLString = ''
UPDATE #MCStaging
SET @SQLString = @SQLString + CAST( PersonID AS varchar ) + ','
PRINT @SQLString

But I really question why you want to do such pivoting. It is efficient
just to return the rows to the client-side or whatever you are doing. If it
is in a temporary table, then better yet you can just reuse it in other
queries or SPs. For some samples using GROUP BY, see my web site under
SQL6x70Scripts.