I would recommend handling this kind of thing outside the database...e.g. pull the resultset into a memory-resident data structure (like an ADO.NET DataTable) then remove all columns from the data...

It sounds like maybe...

May I ask how you ended up with delimited strings in a column? How is this data entering the system? Tools like BCP, BULK INSERT and SSIS are designed to...

Jeff Moden (3/23/2011)

---

In general, I disagree. 😀 There are some very easy steps that can...

belgarion (3/22/2011)

---

Sorry guys. Left out the OPTION (RECOMPILE) in my haste. Added it in. Actually, in many situations you actually find through your performance testing with multiple parameter combinations (which...

sku370870 (3/22/2011)

---

Just as a matter of curiousity ... I know I'll get shot down in flames ... but is what is posted above different from ...

SELECT [SalesOrderID], [OrderDate], [Status],...

Jinx! Diet Coke please Wayne 😎

Just passing along the knowledge...Gail's article is the de facto standard on the topic.

In general dynamic sql is a bad idea and you should avoid it...however your example code is building "catch all query". Have a look at this article:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D

In your case the...

BCP was designed to be fast...not tolerant of bad data or feature-rich.

If you have data quality issues then you can ask for better data or cleanse it after you...

GSquared (3/22/2011)

---

I could be wrong (been a while since I used it), but I seem to remember that xp_cmdshell needs UNCs, instead of mapped drives. Is "G" a mapped...

Try this:

IF OBJECT_ID('test') > 0

DROP TABLE test

GO

CREATE TABLE test (col VARCHAR(20))

GO

INSERT test

(

...

SQL_Padawan_Learner (3/22/2011)

---

parthi-1705 (3/22/2011)

---

hi,

Have a look on this

declare @flg tinyint

Select @flg= case when cmptlevel=100 then 1 when cmptlevel=90 then 2 when cmptlevel=80 then 3 end

from master.dbo.sysdatabases

Select @flg

IF...

mtassin (3/22/2011)

---

Jeff writes great...

I cannot think of how you could know programmatically in T-SQL that the first one occurred...it's a popular topic right now though. Here is a related thread:

http://www.sqlservercentral.com/Forums/Topic1082102-338-1.aspx

As a side note...

Sorry, I misunderstood the original intent behind the question.

sku370870 (3/22/2011)

---

... are [Project 1] and [Project 2] hard-coded?

In the real data...