August 27, 2008 at 3:03 pm
[SQL Server 2005 SP2]
I'm trying to use a stored procedure that returns a scalar output parameter of type XML. I don't want to return a one row, one column result set for consumption by the caller (.Net).
However, I can't populate the output variable if the SELECT contains a WITH clause (either XMLNAMESPACES or a CTE). I get the standard error "Incorrect syntax near the keyword 'with'. If this statement is a ...".
This appears to be a major bug in the T-SQL parser as per the attached T-SQL example just using a variable. In other words, how to you set a scalar variable (of any datatype) using the SET clause with a query that contains a WITH clause?
Any workaround that isn't kludgy?
August 27, 2008 at 3:23 pm
I wouldn't call it a bug as the parser was designed to require the statement prior to WITH to be terminated with a ";" which is why most CTE examples start like this ";WITH". Here are ways to accomplish what you want and I don't think any are kludgy:
[font="Courier New"]DECLARE @xml XML;
;WITH XMLNAMESPACES (DEFAULT 'www.mynamespace.xsd')
SELECT @xml =
(
SELECT * FROM HumanResources.Department
FOR XML PATH ('Department'), ROOT ('Departments'), TYPE
);
SELECT @xml;
GO
DECLARE @xml XML;
;WITH odd_ids AS
(SELECT DepartmentID FROM HumanResources.Department WHERE DepartmentID % 2 <> 0)
SELECT @xml =
(
SELECT * FROM HumanResources.Department d
INNER JOIN odd_ids x ON x.DepartmentID = d.DepartmentID
FOR XML PATH ('Department'), ROOT ('Departments'), TYPE
);
SELECT @xml;
GO
DECLARE @DeptCount INT;
;WITH odd_ids AS
(SELECT DepartmentID FROM HumanResources.Department WHERE DepartmentID % 2 <> 0)
SELECT @DeptCount =
(
SELECT COUNT(*) FROM odd_ids
);
SELECT @DeptCount
GO[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2008 at 3:25 pm
You have to place the CTE first. Like this:
declare @XML XML
;with CTE as
(select 1 as number union all
select 2)
select @xml =
(select max(number) as maxnumber
from cte
for xml raw)
select @xml
Does that allow you to do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 7:04 am
Jack,
Yeah, I managed to come up with the same screwy solution myself. I'm not real happy, but it works.
I submitted the same problem to a Microsoft support forum to see what the people in Redmond come up with.
However, a few points:
1. The SET @var = (subquery) is the proper method for setting the value of a scalar variable. It offers "protection" via an exception should the subquery return more than one row. That is why I use it. It is also the way that Oracle works (subquery that returns more than one row throws an exception).
The other method will actually return all of the rows per the query and set the value of the last row returned -- which can be a.) random and b.) the author of the SQL query has no way of knowing that the query (erroneously) returned more than one row.
2. As for the preceeding semicolon, you don't need to code the WITH statement as ";WITH" -- if you properly terminate all statements with the (now strongly recommended) semicolons.
So your code example actually has a redundant semicolon.
DECLARE @xml XML;
;WITH odd_ids AS
Without the statement terminator (i.e., the semicolon), SQL Server's T-SQL parser has to employ some realy "fuzzy" logic trying to find the end of one command vs. the start of another. I can understand why they are introducing it in all new features. Same as most other languages.
August 28, 2008 at 7:16 am
Just to clarify the following:
The other method will actually return all of the rows per the query and set the value of the last row returned -- which can be a.) random and b.) the author of the SQL query has no way of knowing that the query (erroneously) returned more than one row.
This is what I was refering to:
DECLARE @DepartmentId int;
SELECT @DepartmentId = DepartmentID
FROM HumanResources.Department;
SELECT @DepartmentId;
However, it turns out that the following WILL properly throw an exception:
DECLARE @DepartmentId int;
SELECT @DepartmentId = (SELECT DepartmentID
FROM HumanResources.Department);
So I feel better with the solution albeit it has a double SELECT and I don't know if there is any performance hit.
August 28, 2008 at 7:26 am
Your first point is valid and your second point is valid, but a bit nit-picky as the second ";" does no harm and because most SQL Server developers are not in the habit of terminating their statements so it becomes habit to always use ";With".
You will probably consider these 2 solutions unwieldy as well, but they do work and eliminate the SELECT and use SET instead.
[font="Courier New"]DECLARE @xml XML;
SET @xml = (SELECT
*
FROM
HumanResources.Department d INNER JOIN
(SELECT DepartmentID FROM HumanResources.Department WHERE DepartmentID % 2 <> 0) x ON
x.DepartmentID = d.DepartmentID
FOR XML PATH ('Department'), ROOT ('Departments'), TYPE);
SELECT @xml;
DECLARE @DeptCount INT;
SET @DeptCount = (SELECT
COUNT(*)
FROM
(SELECT DepartmentID FROM HumanResources.Department WHERE DepartmentID % 2 <> 0) x);
SELECT @DeptCount
[/font]
I don't know of a non-kludgy solution to the WITH XMLNAMESPACES query. You could possibly put the result in a temp table or table variable and then use SET. But, and I'm not an XML expert, the FOR XML statement will always return 1 result so using SELECT instead of SET should not be an issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 7:40 am
Jack,
Yes, the non NAMESPACE query works. It is what you'll see published in every [rant: too simple] example.
Since the WITH clause is used for both CTEs as well as defining the XML Namespac(es), I wanted to show that the problem with the SET assigment is not limited to just XML with namespaces. I.e., you can't use CTEs with a variable assignment using SET (subquery). This I consider to be a defect.
As for populating one variable with the initial XML and then trying to add the namespaces via a 2nd pass (ouch! --- performance and memory hit), the thought briefly passed through my mind. You're still stuck syntactically, and I wouldn't be able to sleep at night knowning that I coded such a kludgy and bad performing solution.
August 28, 2008 at 8:07 am
I'm afraid I don't understand the complaint here. There are solutions to get the end result you want, with the error-protection you want. They are easy to implement, documented, and are standard practices.
If you can't sleep well because of using documented, standard, high-performance code, that just doesn't use one unnecessary, non-standard feature, I don't understand that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 28, 2008 at 8:18 am
Re:
I'm afraid I don't understand the complaint here. There are solutions to get the end result you want, with the error-protection you want. They are easy to implement, documented, and are standard practices.
If you can't sleep well because of using documented, standard, high-performance code, that just doesn't use one unnecessary, non-standard feature, I don't understand that.
I'm happy with the solution -- it works and I can live with it as it is using acceptable, good performing logic.
My "bad" feeling was the thought of populating a temp table with an intermediate result and then making a second pass to somehow get the XML namespaces added and then return that as an output parameter. Ugh!
August 28, 2008 at 8:20 am
JohnG (8/28/2008)
Re:I'm afraid I don't understand the complaint here. There are solutions to get the end result you want, with the error-protection you want. They are easy to implement, documented, and are standard practices.
If you can't sleep well because of using documented, standard, high-performance code, that just doesn't use one unnecessary, non-standard feature, I don't understand that.
I'm happy with the solution -- it works and I can live with it as it is using acceptable, good performing logic.
My "bad" feeling was the thought of populating a temp table with an intermediate result and then making a second pass to somehow get the XML namespaces added and then return that as an output parameter. Ugh!
Ah! Now that makes sense. I thought you were making a more general statement. Guess I misread your post.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply