printing 1 to 100 in SQL Server as a batch....

Question

Post reply

printing 1 to 100 in SQL Server as a batch....

Viewing 5 posts - 76 through 79 (of 79 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Jeff Moden (6/30/2009)
Does anyone know if Master.dbo.spt_Values has been deprecated?

Oh. I always thought of it as 'undocumented' anyway...? :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 2

Paul White (6/30/2009)
Jeff Moden (6/30/2009)
Does anyone know if Master.dbo.spt_Values has been deprecated?
Oh. I always thought of it as 'undocumented' anyway...? :unsure:

Heh... yep I know. Just wanted to know if anyone heard anything about it.

As a side bar (or additional handrail :-P), the use of undocumented features has never bothered me because they make changes, without warning, to documented features, as well. Even if you do have some warning (especially on the documented features), it still screws the pooch one way or another. 🙂

--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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Paul White (6/30/2009)
...
That trick with the CASE is clever. I guess my only small concern would be that it depends on SQL Server not evaluating the ELSE when a prior condition is satisfied. I came across something similar a few weeks ago on another forum - with a CONTAINSTABLE full-text predicate which had worked fine on 2005 but broke on 2008. The cause was expression-evaluation order.
My favoured approach to error-generation in a function is to read or write memory outside my address space, so crashing the server*.
Paul
* (weak) joke

If they ever change the way CASE works to do out of order evaluation of expressions, there is going to be a whole lot of code that breaks, with the code I posted before being the least of the problem.

However, this code would work around that by doing the conversion on the result of the CASE expression

select top (100)

row_number() over (order by c1.[object_id]) as n

from

master.sys.columns C1

order by

N,

convert(int,

case when count(*) over () = 100

then ''

-- Generate custom error message using invalid integer conversion

else char(13)+Char(10)+char(13)+Char(10)+char(13)+Char(10)+

' ***** Not enough rows in number table source master.sys.columns ***'+

char(13)+Char(10)+char(13)+Char(10)+char(13)+Char(10) end

)

Paul White SSC Guru Points: 150467 More actions · Answer 4

Michael Valentine Jones (6/30/2009)
If they ever change the way CASE works to do out of order evaluation of expressions, there is going to be a whole lot of code that breaks, with the code I posted before being the least of the problem.

Well that's true I guess. And hey look I fixed the SQRT expression - it was the wrong way around before.

I was pretty sure I tested it... :unsure: Still, it was rather late.

So it comes down to personal preference then really.

Functions really need a better way to 'fail gracefully' though.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Paul White (6/30/2009)
...And hey look I fixed the SQRT expression - it was the wrong way around before.
I was pretty sure I tested it... :unsure: Still, it was rather late.
So it comes down to personal preference then really.
Functions really need a better way to 'fail gracefully' though.
Paul

I have to give you credit for chosing a method that generates just about the most obscure error message possible.

I would love to hide that one in a piece of production code, as long as I knew someone else would have to debug it, and could never trace it back to me. :Whistling: