unwanted Order By side effect

Question

Post reply

unwanted Order By side effect

vlad-548036

Hall of Fame

Points: 3511
More actions
August 11, 2009 at 2:06 pm

#213276

Hi,
RE:SQL Server 2005
Here's a script generator to enable all of the sql logins except sa:
declare @STR varchar(max);
set @STR='';
select @STR=@str+'alter login '+name+' enable;
'
from sys.server_principals where type='S' and name!='sa'
-- order by 1;
select @STR;
exec (@str);
However! If you comment in order by clause - it stops working. Only the last login gets scripted.
Any idea why ???

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

But if you change the ORDINAL (1) to [name] it works.

You really should start using the column name(s) in your order by clauses.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

indeed ... as Lynn stated, use the column 'name' to order the result set and you'll be fine.

For you query the ordinal column 1 is the variable value for the first result row.

If you use the colmn name, this can be avoided.

order by name;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 3

to be clear, that is not a production code. Column names have been used in production for over decade now. yes order by name works, ...

Let me restate the question: Why does it not work with ordinal ??

Is it a bug ??

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'd have to say it isn't a bug as sorting by ordinal works fine with a normal query. Probably has to do more with the concatination process.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Also, the following works:

declare @ EnableStr varchar(max);

select

@ EnableStr = (select 'alter login ' + [name] + ' enable;'

from sys.server_principals

where type = 'S' and name 'sa'

order by 1

for xml path(''));

select @ EnableStr;

Be sure to remove the space between the @ and name of the variable. For some reason, I can't post this code with out the space. Really weird, as I don't have this problem at home just here at work on either of my desktop systems.

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 6

vlad-548036

Hall of Fame

Points: 3511

August 11, 2009 at 3:26 pm

#1037757

Thanks for your time.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 7

Heh... ALL code is production code... maybe just not yet. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 8

Jeff Moden (8/11/2009)
Heh... ALL code is production code... maybe just not yet. 😉

Too funny...too true.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

When you use "ORDER BY [name]" the execution plan shows it sorting the input rows and feeding them to the Compute Scalar steps to evaluate the expression, and it performs as expected.

When you say "ORDER BY 1" the execution plan shows it evaulates the expression before sorting. This only makes sense if the expression is using the pre-query version of @STR on each row independently (i.e. concatenating every row with an empty string). if you remove "@str+" from the expression you get the same result, and the execution plan for that also shows the Sort step coming last.

You can find a page in Books Online for "SELECT @local_variable", but it doesn't cover "@var=@var+" expressions. I don't think it is a feature of ANSI-standard SQL as it implies a row-by-row context is maintained during set operations. Most of us know how it works, many of us have used it, but since it is an undocumented feature there is no guarantee it will always work.

A version that doesn't use any undocumented features (and doesn't care if you ORDER BY [name] or ORDER BY 1) is:

SELECT REPLACE( SUBSTRING(

( SELECT '|ALTER LOGIN [' + [name] + '] ENABLE;'

FROM sys.server_principals

ORDER BY [name]

FOR XML PATH('')), 2, 9999), '|', '

')

vlad-548036 Hall of Fame Points: 3511 More actions · Answer 10

Heh... ALL code is production code... maybe just not yet.

- unless there is a DBA that will prohibit it:-D