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

Question

Post reply

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

Viewing 15 posts - 16 through 30 (of 79 total)

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

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

Sinshith S Anand (6/23/2009)
Please contribute ur ideas...I have got only one correct answer....

Really? What is your definition of correct?

I've seen answers here that meet your requirements even if they aren't the best.

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

jdurandt (6/25/2009)
Here is one that uses a cartesian product:

I love this one! Would use UNION ALL instead of UNION, but still...:-)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

According to OP, he wants the numbers PRINTED, because they then are stored in the log file.

N 56°04'39.16"
E 12°55'05.25"

jdurandt SSChasing Mays Points: 628 More actions · Answer 4

I took the "OP requires a PRINT" as a challenge... 😀

How about this?

Set NOCOUNT ON

Declare @result varchar(8000)

set @result = ''

Declare @Numbers Table (val int)

Insert into @Numbers

Select Ones.val + Tens.val

from (Select 0 as val

union Select 1

union Select 2

union Select 3

union Select 4

union Select 5

union Select 6

union Select 7

union Select 8

union Select 9) AS Ones

cross join

(Select 0 as val

union Select 10 as val

union Select 20

union Select 30

union Select 40

union Select 50

union Select 60

union Select 70

union Select 80

union Select 90) AS Tens

Where Ones.val + Tens.val >= 1

Order by Ones.val + Tens.val

Update @numbers

Set val = val,

@result = @result + Cast(val as varchar) + char(13)

print @result

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Peso (6/26/2009)
According to OP, he wants the numbers PRINTED, because they then are stored in the log file.

I considered that, but seeing as one of his initial examples just had a select and no print, I figured he just wanted them displayed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Peso (6/26/2009)
According to OP, he wants the numbers PRINTED, because they then are stored in the log file.

create database [04C87CAC-9AA8-4465-AC6E-921060A604FF];

go

alter database [04C87CAC-9AA8-4465-AC6E-921060A604FF] set recovery simple;

go

use [04C87CAC-9AA8-4465-AC6E-921060A604FF];

go

checkpoint

select * from fn_dblog(null, null)

print 'hello'

select * from fn_dblog(null, null)

go

use master

go

drop database [04C87CAC-9AA8-4465-AC6E-921060A604FF];

? :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Sinshith S Anand (6/25/2009)
Still many methods are there.....Please contribute your ideas

Ummmmm... WHY? You writing a book or what?

See ya...

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

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

Paul White (6/24/2009)
Fastest possible implementation:

I take it all back 🙁

This is faster:

[font="Courier New"]PRINT 1

PRINT 2

PRINT 3

...

PRINT 100[/font]

You can have that one for your book for free!

Paul

edit: stupid font tags :angry:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sinshith S Anand SSChasing Mays Points: 605 More actions · Answer 9

Hi friends,

I am really happy to see Lots of ideas....You can visit my blog also

http://sinshith.wordpress.com/

And let me know your comments...I started writing blog recently

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

Sinshith S Anand (6/26/2009)
Hi friends,
I am really happy to see Lots of ideas....You can visit my blog also
http://sinshith.wordpress.com/
And let me know your comments...I started writing blog recently

You still haven't answered my question. What do you consider correct??

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 11

I think you need to test the solutions in your blog first.

The "Procedure for droping all constraints from a table" won't work because of this (amongst other things):-

WHILE(@@FETCH_STATUS-1)

It would also be a good idea to state which version of SQL Server they apply to... DATE and DATETIME2 aren't valid in all versions.

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

Credit where it's due for giving it a go. 🙂

I agree that you should personally test code you post - for credibility's sake.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 13

Credit where it's due for giving it a go

Hope my post didn't sound too critical... it was meant as constructive criticism:blush:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Ian Scarlett (6/26/2009)
I think you need to test the solutions in your blog first.
The "Procedure for droping all constraints from a table" won't work because of this (amongst other things):-
WHILE(@@FETCH_STATUS-1)

Also foreign keys have to be dropped before the primary keys/unique constraints that they reference.

Are you intending to put all these print 1..100 answers on your blog as well?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Ian Scarlett (6/26/2009)
Credit where it's due for giving it a go
Hope my post didn't sound too critical... it was meant as constructive criticism:blush:

I read it as constructive criticism 🙂 😎

edit: reworded to clarify my meaning

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi