You're trying to combine the function of EXEC where it executes an ad hoc sql string with the function of exec where it captures the return status of the execution...

DBA's & Developers, yeah, they should get it because it does have the tools they need to get the job done.

End-users... Why on earth are the end-users generating ad hoc...

Or you could use COALESCE

INSERT INTO [dbo].[TableA]

([Col1],

[Col2],

[Col3])

SELECT

COALESCE([Col1],'unknown'),

COALESCE([Col2],'unknown'),

COALESCE([Col3],'unkown'

FROM [dbo].[TableB];

If you're using the VALUES statement on insert, you can use the default value:

VALUES

(DEFAULT,

'Something',

'Something)

But if you really wanted the defaults to...

insane_professional (10/22/2007)

---

hi, i think its a pretty simple task but i just cant seem to get it right as I am pretty new to SQL.

What i want to do is...

You can do this with client side code in VB. It works, but it's not the best choice since you're sacrificing the power of SQL Server in order to put...

Sorry, I'm stumped. I couldn't do both. I got to learn a bit about the query and value methods of xml data types that I wasn't that aware of, but...

ecreese (10/19/2007)

---

Grant when I run your query I get this error

Msg 421, Level 16, State 1, Line 1

The xml data type cannot be selected as DISTINCT because it is not...

I tested using the original query, not the DISTINCT. Hang on.

Oops.

Got it. Add this to the inner join:

FOR XML PATH (''), type

so it looks like this:

SELECT DISTINCT LocalID as "@ID"

, (SELECT ItemUrl

FROM foo X

WHERE f.LocalID=x.LocalID

ORDER BY x.photoseq

FOR XML PATH(''),...

Just so I had some data to mess with, I modified your query to use something form AdventureWorks:

SELECT A.ProductId "@ID"

,a.Name "Col1"

, (SELECT x.StandardCost

FROM [Production].[ProductCostHistory] X

WHERE A.ProductId=X.[ProductID]

ORDER BY X.StartDate

FOR XML PATH('')

)...

Until we get the 2008 MERGE statement, you need to do three passes through the data, joining on the incoming data such that you do a batch of updates where...

Luke L (10/19/2007)

---

Luke L (10/19/2007)

---

Well let's see, I started off with 2.5 years attempting a Sound Engineering Degree (The goal was to go work for Skywalker sound out on the...

I still don't understand what you mean by tasks & processes. What those are and what you're attempting to compare between the database and the front-end?

Do you mean the queries...

I'm one of those flaky individuals who kind of backed into this gig. I did four years in the Navy. I was effectively an inheritor of Robert Fulton, working on...

You could try creating a table valued user defined function that uses dynamic sql to run a query against the table you have and then generate a select statement. The...