February 26, 2019 at 2:18 pm
I am writing some fairly complicated dynamic SQL for a stored procedure that needs to be multi-purpose. As such, I want to flexibly insert the list of columns from my target table and from my source table into a string that I will then execute. I know how to do the concatenation with FOR XML PATH using STUFF to concatenate, but the results always come back in alphabetical order by column name, not by the order of the column in the table, which would be column_id. Here is what I am doing so far:
declare @Cols nvarchar(max), @SQL nvarchar(max)
SELECT c.column_id
,c.name 'ColumnName'
into #tmp_columns
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('MYTABLENAME')
and (i.is_primary_key = 0 or i.is_primary_key is null)
order by c.column_id asc --- this doesn't seem to help
select @Cols = stuff((select ', ' + quotename(ColumnName) from (select distinct ColumnName from #tmp_columns) X
for XML PATH('')),1,2,'')
print @cols
Once I can see the @Cols string come back in column_ID order I will know what to do to insert it in my dynamic SQL insert statement but I need this last bit of help. I haven't found anything like this on the web and using ORDER BY anywhere in this mess is not allowed.
Thanks to all of you on these forums who help folks like me.
February 26, 2019 at 2:38 pm
ok, so just to give anyone else with this problem the answer (yes I figured it out myself!!) all I had to do was to include the column_id in the statement:
select @Cols = stuff((select ', ' + quotename(ColumnName) from (select distinct Column_ID, ColumnName from #tmp_columns) X
for XML PATH('')),1,2,'')
worked like a charm. I didn't realize that adding a column that is sorted properly in the first place provides the right output.
February 26, 2019 at 3:25 pm
The ORDER BY used in filling the temp table has no effect on the column order in the XML result, because the DISTINCT keyword in your subquery (SELECT DISTINCT ColumnName FROM #tmp_columns) is forcing a sort by column name. DISTINCT is also unnecessary, column names within tables have to be unique. And why even use a subquery here?
You can put ORDER BY Column_ID right before FOR XML PATH('') to enforce the desired order. An explicit ORDER BY would be preferable to relying on the order imposed by DISTINCT, even if adding Column_ID to the subquery did fix the problem.
SELECT @Cols = STUFF((
SELECT ', ' + QUOTENAME(ColumnName)
FROM #tmp_columns
ORDER BY Column_ID
FOR XML PATH('')), 1, 2, '')
using ORDER BY anywhere in this mess is not allowed.
What makes you say that?
February 26, 2019 at 5:10 pm
I consider the following construct a bit simpler and does not require the temp table
and output gets ordered by column_id as required
code based on the OP. Does have the issue that if there are no indexes on the table it will output all columns of the table which I do not think is what is desired here. (but maybe I'm wrong)
declare @Cols nvarchar(max)
select @Cols = stuff(
(select ', ' + quotename(c.name)
from sys.columns c
inner join sys.types t
on c.user_type_id = t.user_type_id
left outer join sys.index_columns ic
on ic.object_id = c.object_id
and ic.column_id = c.column_id
left outer join sys.indexes i
on ic.object_id = i.object_id
and ic.index_id = i.index_id
where c.object_id = base.object_id
and (i.is_primary_key = 0 or i.is_primary_key is null)
order by c.column_id
for xml path ('')
), 1, 2, '')
from (select object_id('MYTABLENAME') as object_id) base
select @Cols
February 26, 2019 at 5:33 pm
Or something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITION
SET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;
February 27, 2019 at 6:31 am
Jonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;
Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎
February 27, 2019 at 6:56 am
Eirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎
I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?
February 27, 2019 at 9:10 am
Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?
Let me have a look Jonathan, I've had this discussion with Itzik Ben-Gan and few others
😎
February 27, 2019 at 10:00 am
Jonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?
Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 27, 2019 at 10:15 am
drew.allen - Wednesday, February 27, 2019 10:00 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?
February 27, 2019 at 11:08 am
Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AMdrew.allen - Wednesday, February 27, 2019 10:00 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?
I've not found that either but Microsoft hasn't conformed on it so I cannot recommend it.
😎
February 27, 2019 at 11:48 am
Jonathan AC Roberts - Wednesday, February 27, 2019 10:15 AMdrew.allen - Wednesday, February 27, 2019 10:00 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?
Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says
To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 27, 2019 at 11:55 am
drew.allen - Wednesday, February 27, 2019 11:48 AMJonathan AC Roberts - Wednesday, February 27, 2019 10:15 AMdrew.allen - Wednesday, February 27, 2019 10:00 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says
To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.
Drew
But that is something totally different from the query in my post. This is not an update to a database, it's setting the value of a string variable. You can change the order by to any order on any column(s) and it works.
February 27, 2019 at 12:31 pm
Jonathan AC Roberts - Wednesday, February 27, 2019 11:55 AMdrew.allen - Wednesday, February 27, 2019 11:48 AMJonathan AC Roberts - Wednesday, February 27, 2019 10:15 AMdrew.allen - Wednesday, February 27, 2019 10:00 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says
To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.
Drew
But that is something totally different from the query in my post. This is not an update to a database, it's setting the value of a string variable. You can change the order by to any order on any column and it works.
Is it? They both use the quirky update method. There are proven problems with the one, which indicates that there may also be problems with the other. And just because it has performed a certain way in the past, doesn't mean that it will always perform a certain way in the future, especially since it is undocumented and unsupported, which means that MS won't test to see whether an update "breaks" this functionality.
I see no benefit to using this method over the XML concatenation and lots of risk. I simply don't think it's worth the risk.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 27, 2019 at 12:54 pm
drew.allen - Wednesday, February 27, 2019 12:31 PMJonathan AC Roberts - Wednesday, February 27, 2019 11:55 AMdrew.allen - Wednesday, February 27, 2019 11:48 AMJonathan AC Roberts - Wednesday, February 27, 2019 10:15 AMdrew.allen - Wednesday, February 27, 2019 10:00 AMJonathan AC Roberts - Wednesday, February 27, 2019 6:56 AMEirikur Eiriksson - Wednesday, February 27, 2019 6:31 AMJonathan AC Roberts - Tuesday, February 26, 2019 5:33 PMOr something even simpler like this should work:DECLARE @Cols NVARCHAR(MAX)=''
SELECT @Cols +=QUOTENAME(c.column_name)+', '
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
ORDER BY c.ORDINAL_POSITIONSET @Cols =LEFT(@Cols ,LEN(@Cols)-1);
PRINT @Cols ;Quick thought, it has been stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method, hence the promotion of the FOR XML method. An example would be a parallel execution where the gathering of the streams would not align.
😎I think all that query is doing is concatenating a string with the order specified with ORDER BY. It should be no different to writing a query with an ORDER BY in and displaying results to a grid.
Can you provide any links to "stated by several MS people and SQL Server MVPs that even using the order by clause, Microsoft does not guarantee the order of appearance when using this method" or an example where this doesn't work?Jeff Moden has an article that talks about what needs to be in place for the Quirky Update to work properly. You don't have everything in place that you need.
Furthermore, I see no reason to use an undocumented, unsupported "feature" when there is a perfectly acceptable documented and supported feature.
Drew
I couldn't see anything that related to the statement in my answer there. Can you find an example of where this wouldn't work?
Are you talking about the "quirky update" as an undocumented, unsupported feature or concatenating a string with a select from a table?Read the section labeled ORDER BY and Extra "Warm Fuzzies" specifically the part where it says
To summarize, you cannot force an ordered update using ORDER BY or an INDEX HINT. If a Clustered Index is present, it will still do the update in Clustered Index order. If you don't have a Clustered Index on the table, you'll simply get bad answers.
Drew
But that is something totally different from the query in my post. This is not an update to a database, it's setting the value of a string variable. You can change the order by to any order on any column and it works.
Is it? They both use the quirky update method. There are proven problems with the one, which indicates that there may also be problems with the other. And just because it has performed a certain way in the past, doesn't mean that it will always perform a certain way in the future, especially since it is undocumented and unsupported, which means that MS won't test to see whether an update "breaks" this functionality.
I see no benefit to using this method over the XML concatenation and lots of risk. I simply don't think it's worth the risk.
Drew
Yes it's different, the problem with the quirky update is that it was updating a table on its primary key. This is just selecting the values with a select statement and concatenating them into a string variable in the order of the select, it's no different from the order they are in the select.
I've found this link which says:
If a SELECT statement returns more than one row and the variable references a non-scalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:USE AdventureWorks2014;
GO
DECLARE @EmpIDVariable int;
SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;
SELECT @EmpIDVariable;
GO
This is saying that the ORDER BY does work.
From SQL 2017 onward there is a better (documented) method to this using the STRING_AGG() function:DECLARE @Cols nvarchar(MAX)
SELECT @Cols=STRING_AGG(QUOTENAME(c.COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY c.ORDINAL_POSITION ASC)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME='MYTABLENAME'
AND c.TABLE_SCHEMA='dbo'
PRINT @Cols
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply