Post reply

Dynamic SQL

  • 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

     

  • February 20, 2007 at 1:54 am

    #690589

    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

  • February 20, 2007 at 1:58 am

    #690590

    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

  • February 20, 2007 at 11:20 am

    #690713

    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.
    ******************

  • February 20, 2007 at 7:29 pm

    #690790

    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)
    Intro to Tally Tables and Functions

  • February 20, 2007 at 11:06 pm

    #690811

    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

     

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

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