gmrose (10/21/2010)

---

Thank you again to Lutz and Wayne. I updated my script per Lutz' suggestions to use table #tmpChecks instead of table variable @tmpChecks. I made some minor...

That explains the bad performance pretty much...

You're using a table variable just like I did for my demo version. :blush: My fault. I'm sorry.

A table variable is a bad option...

gmrose (10/20/2010)

---

After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been...

No, I haven't. And I probably won't either.

Such...

You might want to compare the performance of your loop with the following code.

I can't tell you if it'll return the same results since I have nothing to test against,...

It's slightly more complicated than just using a CTE...

DECLARE @tbl TABLE

(

ckid INT,stat VARCHAR(10)

)

INSERT INTO @tbl

SELECT 101 ,'Open' UNION ALL

SELECT 102 ,'Open' UNION ALL

SELECT 103 ,'Open' UNION...

Which one did you try? T-SQL or CLR?

Something along those lines?

SELECT

id,

CASE

WHEN EXISTS (SELECT 1 FROM #TblTemp t2 WHERE t1.id=t2.id AND Price=9999.9999)

THEN 1111.1111

ELSE AVG(Price) END AVGPrice

FROM #TblTemp t1

GROUP BY Id

Did you search this site for "DelimitedString8K" on this site?

This is a function posted by Jeff Moden. If you get the "right thread" (don't recall the link right now) you'll...

I followed the link you posted and it seems like the OP is not erally...

Is it just because I'm in a bad mood today or why do I have the feeling that todays questions are more demanding with even less effort than on other...

Uripedes Pants (10/20/2010)

---

I think he probably gave up because you still haven't filled the request for easily consumable create table statements and test data. When I looked at the...

What's so funny about it? I found it rather sad... :crying:

You need to use XQuery.

Side note: A great resource for several XQuery solutions is Jacob Sebastians blog

DECLARE @tbl TABLE

(

id INT,

col XML

)

INSERT INTO @tbl

SELECT 1,'<Product>

<name>car</name>

<color>red</color>

<type>sedan</type>

</Product>'...

Your requirement is not clear.

What do you mean by "auto generate"? Are you looking for some kind of an identity column in a custom format?

To ensure you only have unique...