Getting results in seperate sets

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)

Getting results in seperate sets

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 06:09:33

Hello,
I have a string of values of numbers in a table.

How do i select them in multiple sets of 500, ie


declare @set varchar(max), @x int
set @x = 1

while @x =< 10
begin
set @set = --- Get the values out in multiples of 500 or any other number

Print 'This is set '+convert(@x as varchar(2))+' : '+@set
set @x = @x + 1
end

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 06:18:07

[code] declare @x int
SELECT @x= CASE WHEN (COUNT(*)%500)=0 THEN (COUNT(*)/500)
ELSE (COUNT(*)/500) + 1
END
FROM YourTable

;With CTE (Seq,Numberfield) AS
(SELECT ROW_NUMBER() OVER(ORDER BY NumberField) AS Seq,NumberField
FROM YourTable
)
WHILE @x >0
BEGIN
SELECT * FROM CTE WHERE Seq BETWEEN ((@x -1)*500) + 1 AND @x*500
SET @x=@x-1
END[/code]

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 06:23:42

Sorry,
what is Numberfield, the column i want to retrieve ? and what is seq and cte ?

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 07:01:13

quote:
Originally posted by mary_itohan

Sorry,
what is Numberfield, the column i want to retrieve ? and what is seq and cte ?

_____________________

Yes O !

numberfield is column of table which has numbers which you want to retrieve as multiples of 500
seq is a rownumber which is dynamically done to group records as per multiples of 500
cte is common table expressions. it just used to generate the unique numbered field

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 08:18:37

sorry sir, but there is an error

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WHILE'.

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 08:22:05

ok then try like this:-

declare @x int
 SELECT @x= CASE WHEN (COUNT(*)%500)=0 THEN (COUNT(*)/500)
                 ELSE  (COUNT(*)/500) + 1
                 END
 FROM YourTable

WHILE @x >0
BEGIN
SELECT * FROM YourTable 
WHERE NumberField BETWEEN ((@x -1)*500) + 1 AND @x*500
SET @x=@x-1 
END

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 08:27:05

I get this error

Msg 248, Level 16, State 1, Line 9
The conversion of the varchar value '278312345678' overflowed an int column. Maximum integer value exceeded.

So i changed the code in line 9, to convert it to varchar(max) but still getting the error



declare @x int
 SELECT @x= CASE WHEN (COUNT(*)%500)=0 THEN (COUNT(*)/500)
                 ELSE  (COUNT(*)/500) + 1
                 END
 FROM history

WHILE @x >0
BEGIN
SELECT cast(receipient as varchar(max)) FROM history
WHERE receipient BETWEEN ((@x -1)*500) + 1 AND @x*500
SET @x=@x-1 
END

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 08:32:08

is the number field a varchar column? why is it declared varchar if it holds numeric values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 08:34:22

anyways, if its varchar try like below

declare @x bigint
 SELECT @x= CASE WHEN (COUNT(*)%500)=0 THEN (COUNT(*)/500)
                 ELSE  (COUNT(*)/500) + 1
                 END
 FROM YourTable

WHILE @x >0
BEGIN
SELECT receipient  FROM YourTable 
WHERE cast(receipient as bigint)  BETWEEN ((@x -1)*500) + 1 AND @x*500
SET @x=@x-1 
END

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 08:38:05

(0 row(s) affected)

am confused :-(

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 08:50:51

quote:
Originally posted by mary_itohan

(0 row(s) affected)

am confused :-(

_____________________

Yes O !

ok. show your sample data from table and show how you want output

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 08:58:53

Try this also:-

CREATE TABLE #Temp
(Seq bigint,
 receipient bigint
) 
declare @x bigint
 SELECT @x= CASE WHEN (COUNT(*)%500)=0 THEN (COUNT(*)/500)
                 ELSE  (COUNT(*)/500) + 1
                 END
 FROM YourTable

INSERT INTO #Temp (Seq,receipient)
SELECT ROW_NUMBER() OVER(ORDER BY cast(receipient as bigint)),
cast(receipient as bigint)
FROM YourTable

WHILE @x >0
BEGIN
SELECT * FROM #Temp WHERE Seq BETWEEN ((@x -1)*500) + 1 AND @x*500
SET @x=@x-1 
END

mary_itohan
Posting Yak Master

191 Posts

Posted - 2008-10-05 : 09:30:24

Ok great it works.

I will integrate the other script your wrote for me into it.

thank you

_____________________

Yes O !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 09:48:45

quote:
Originally posted by mary_itohan

Ok great it works.

I will integrate the other script your wrote for me into it.

thank you

_____________________

Yes O !

welcome

Dont hesitate to ask if you've any more doubt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources