Issue with Replace() and OrderBy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Issue with Replace() and OrderBy

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-08-11 : 16:00:54

Hi All,
Having an issue with using Replace and OrderBy together in an @var = @var + query. What am I missing here?

/*Works but need to use Replace() in case a column has spaces in it.*/
Declare @UpdateInputParameters VARCHAR(8000)
SET @UpdateInputParameters = ''

Select
@UpdateInputParameters = @UpdateInputParameters + ',@' + A.Column_Name

From
Information_Schema.Columns A
Where
A.Table_Name = 'syslogins'
order by ordinal_position asc

select @UpdateInputParameters

/*Works but columns not in right order*/
Declare @UpdateInputParameters VARCHAR(8000)
SET @UpdateInputParameters = ''

Select
@UpdateInputParameters = @UpdateInputParameters + ',@' + Replace(A.Column_Name ,' ','')

From
Information_Schema.Columns A
Where
A.Table_Name = 'syslogins'

select @UpdateInputParameters

/*Does not work only getting the last column*/
Declare @UpdateInputParameters VARCHAR(8000)
SET @UpdateInputParameters = ''

Select
@UpdateInputParameters = @UpdateInputParameters + ',@' + Replace(A.Column_Name ,' ','')

From
Information_Schema.Columns A
Where
A.Table_Name = 'syslogins'
order by ordinal_position asc

select @UpdateInputParameters

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-11 : 16:52:11

I have seen this before; it's an "undocumented feature". The ORDER BY seems to mess it up.

Try it like this, using a TOP with the ORDER BY in the derived table. You could also just put the data in a temp table first.


Declare @UpdateInputParameters VARCHAR(8000)

Select
	@UpdateInputParameters =
		isnull(@UpdateInputParameters+',@','') +
		Replace(A.Column_Name ,' ','')
from
	(
	select	top 100 percent
		A1.Column_Name
	From 
		Information_Schema.Columns A1
	Where 
		A1.Table_Name = 'syslogins'
	order by 
		A1.ordinal_position asc
	) A

select @UpdateInputParameters