select row - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

select row

Author

Topic

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-30 : 00:31:50

hi
if i need to get only the top 75 i give select top 75 columnname from tablename in my proc
now i need to declare input the select according to me say 15 40 68 any number in the proc
declare
@top int
select @top tpa_id,empe_key,* from dbo.[dept]

krmm

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 00:37:02

set rowcount @rows

select * from dbo.[dept] order by somecol

set rowcount 0

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 00:40:53

use set rowcount


use pubs

declare @rows	int

select	@rows = 10
set rowcount  @rows
select * from sales

select	@rows = 20
set rowcount  @rows
select * from sales

-- remember to set back to 0 for all
set rowcount 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 00:42:08

Got distracted by . . .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 00:44:26

If using SQL Server 2005,

SELECT TOP (@Rows) * FROM MyTable ORDER BY SomeCol

Peter Larsson
Helsingborg, Sweden

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-30 : 01:11:40

dont u have in sqlserver 2000 SELECT TOP (@Rows) * FROM MyTable ORDER BY SomeCol

declare @rows int
set rowcount @rows
select * from sales
that give me error
Server: Msg 507, Level 16, State 2, Line 2
Invalid argument for SET ROWCOUNT. Must be a non-null non-negative integer.
i need to get only the supplied number from the parmeter

krmm

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-30 : 01:20:38

You got an error because you didn't supply a value for @rows.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 01:20:43

The error message is self-explained!

The @rows variable should be the parameter you talked about in the original posting.
If you want to test first, set @rows to a value of your choice!

declare @rows int
set @rows = 10
set rowcount @rows
select * from sales

Once again, when given suggestions and you don't understand them, please read about the specific command or statement in Books Online.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 01:21:58

quote:
dont u have in sqlserver 2000 SELECT TOP (@Rows) * FROM MyTable ORDER BY SomeCol

Not in SQL Server 2000.


declare @rows int
-- you did not initialize the @rows
select @rows = 10
set rowcount @rows
select * from sales
set rowcount 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 01:26:06

again

And weird also. Similar modification, Same color coding

oracle_corrgi
Yak Posting Veteran

98 Posts

Posted - 2007-01-30 : 01:26:12

in my select statment i dont need to send the row=10 if that is the case if i need 75 then i need to put row=75 is that i will be send the ro count from the application
bascically i need to select the rowcount and insert into temp table say 20 30 15 differ's

krmm

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 01:29:09

If always selecting 75 first rows,

SELECT TOP 75 * FROM Sales ORDER BY {SomeCol}

But this is NOT what you requested in the original posting...

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-30 : 01:32:03

quote:

now i need to declare input the select according to me say 15 40 68 any number in the proc

Pass the required rows number 15, 40 or 68 into the variable @rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 01:33:13

[code]CREATE PROCEDURE uspGetMySales
(
@NumRows INT = 10
)
AS

SET NOCOUNT ON

IF @NumRows IS NULL
SELECT @NumRows = 10

SET ROWCOUNT @NumRows

SELECT *
FROM Sales
ORDER BY {SomeCol} DESC

SET ROWCOUNT 0[/code]Now this stored procedure accepts a parameter. Send a value from your application how many rows you want to be returned.
If no value is sent, or NULL is sent, the procedure defaults to 10 rows.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-30 : 01:35:21

If you don't know how to create code in VB to call the stored procedure,
have a look at this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources