Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Unanswered: Parameter SP X TOP

    Hy Guys,

    I`m runing a very simple problem, that I can`t solve.

    I have a procedure like this:

    CREATE PROCEDURE spu_GetData (@numerOfRows Numeric(07)) AS

    SELECT TOP @numerOfRows
    FROM MyTable

    GO

    SQL, don`t accepts the parameter @numerOfRows to the TOP x...

    Any Suggestions ??
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  2. #2
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Re: Parameter SP X TOP

    Create the SQL statement dynamically and then EXECute it...

    DECLARE @tsql varchar(1000)

    SET @tsql = '
    SELECT TOP ' + ltrim(str(@numberOfRows)) + ' *
    FROM MyTable'

    EXEC (@tsql)


    Originally posted by Diogo
    Hy Guys,

    I`m runing a very simple problem, that I can`t solve.

    I have a procedure like this:

    CREATE PROCEDURE spu_GetData (@numerOfRows Numeric(07)) AS

    SELECT TOP @numerOfRows
    FROM MyTable

    GO

    SQL, don`t accepts the parameter @numerOfRows to the TOP x...

    Any Suggestions ??
    HueyStLoui

  3. #3
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    Humm.. I had thinked abou this possibility.

    But,
    This is a SELECT from a CURSOR...

    And the procedure is quite big, have 5 cursors and almost 250 lines!

    Can I create the statement dinamically only for the Query of this Cursor ? That is a piece of the stored procedure??
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  4. #4
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19
    Sure, small example...

    declare @irows int
    declare @tsql varchar(1000), @company varchar(64)
    declare @curTest cursor

    set @irows = 10
    set @tsql = 'declare curTemp cursor for
    select top ' + ltrim(str(@irows)) + ' company
    from contact1'

    exec (@tsql)
    set @curTest = curTemp
    open @curTest
    fetch @curTest into @company
    while (@@fetch_status = 0)
    begin
    print @company
    fetch @curTest into @company
    end
    close @curTest
    deallocate curTemp


    Originally posted by Diogo
    Humm.. I had thinked abou this possibility.

    But,
    This is a SELECT from a CURSOR...

    And the procedure is quite big, have 5 cursors and almost 250 lines!

    Can I create the statement dinamically only for the Query of this Cursor ? That is a piece of the stored procedure??
    HueyStLoui

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •