Arjun Sivadasan (8/30/2010)

---

I can't use batch processing as I need the entire set of recordids at one go. This is only a small part of the query and I have...

Brandie Tarvin (8/30/2010)

---

Drew, the code author had no specific transactions within the TRY block. He's treating the TRY as all one transaction. It should be an All or Nothing proposition....

Your pseudo-code doesn't have an explicit transaction defined and you didn't mention whether you had changed your transaction management mode, so I'm assuming that it's using the default which is...

I only suggested SET ROWCOUNT because I...

Actually, I was thinking about this last night, and because of the number of records you may want to do batch the processing. Here's one example. You would...

I tend to use FOR XML to concatenate values together. In some cases, you can also use the quirky update.

Here is the FOR XML version.

if object_id('tempdb..#test') is not null...

If you want to return fields that you don't want to group on, a CTE with Row_Number() is often a good approach.

I changed your Table to a Table variable, but...

SwePeso (8/25/2010)

---

DECLARE@Sample TABLE

(

ID INT NOT NULL

)

INSERT@Sample

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 9 UNION ALL

SELECT11

-- Get the missing...

The Stuff() formula is used to get rid of the extra comma.

Drew

I've never used full-text search, but I think that it has the capabilities that you are looking for. It's certainly a good place to start.

Drew

Shorter is not always simpler. If you're going for shortest, then this is much more elegant.

SELECT DateAdd(Day

, Substring(@Source, 5, 3) - 1

...

You're returning a varchar field. Is there some reason that you don't want to return a datetime field?

When you assign your datetime field to your varchar field, you don't...

Just because environment_key is the only field that the three have in common doesn't mean that's the field that you want to join on. All of my tables have...

This makes it seem like 1 is a special case when it's actually an exemplar of...

khurram sultan (8/19/2010)

---

SELECT

Tb_item.Item_part_number ,

Tb_item.item_description,

(SELECT TOP 1 ISNULL(Stock, 0) FROM Tb_Item_Cost_Ledger WHERE (ItemId = Tb_Item.CoA_ID) AND (Vdate <= '20100101') ORDER BY Vdate DESC) as Stock

FROM ...