Dynamic SQL

Question

Post reply

Dynamic SQL

Schalk Lubbe

SSC Veteran

Points: 216
More actions
February 20, 2007 at 12:17 am

#115705

We have a situation where we want to limit the resulting nomber of rows of data that is returned to a client over the WAN. So I want to do something like the following:
DECLARE @MaxRow
SET @MaxRows = 20
SELECT TOP @MaxRows From SomeTable WHERE blah, blah, blah
Unfortunately this is not supported directly, and I don't want to go the route of using SET ROWCOUNT = 20.
Is there any bright person who can suggest an alternative?
Schalk Lubbe

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

Can u try the following way:

create

procedure spGetRows

(

@TopRows int)

as

begin

set rowcount @TopRows

select *

from northwind..customers with (nolock)

set rowcount 0

end

go

usage

:

exec spGetRows @TopRows = 10

Kshitij Sah Old Hand Points: 319 More actions · Answer 2

I think this will work.

DECLARE @MaxRows NVARCHAR(300)

DECLARE @sql NVARCHAR(300)

SET @MaxRows = 20

set @sql = 'select top ' + @MaxRows + '* from SomeTable'

EXEC sp_executesql @sql

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 3

I would rather use the SET ROWCOUNT method than the dynamic SQL. Dynamic SQL is a different beast by itself.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Why can't you or don't you want to use SET ROWCOUNT @MaxRows???

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

Schalk Lubbe SSC Veteran Points: 216 More actions · Answer 5

Thanks, everybody, for your suggestions.

I want to have a setting on the server, saved in a table, which can be modified by a system administrator.

Because our system uses triggers to fire audit trail updates, I am concerned that using SET ROWCOUNT could have a negative impact.

I would also prefer not to use dynamic SQL, because the system is very stored-procedure intensive, so everything is tuned to perfection for rapid response times. If I start replacing every SELECT statement with a dynamic SQL statement, I'll lose all of that fine tuning.

Schalk Lubbe