Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: I couln't return a table from a procedure . Pls Help me

    I couln't return a table from a procedure . Pls Help me

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Can you send the procedure ? Would help

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I didn't think (again?) you could...

    I tried

    Code:
    USE Northwind
    Go
    
    CREATE PROC mySproc 
    	@Results TABLE ([name] sysname) OUTPUT
    AS
    
    INSERT INTO @Results ([name]) SELECT [name] FROM sysobjects
    GO
    
    EXEC mySproc
    And that didn't work...then I rea din BOL

    E. Use an OUTPUT cursor parameter
    OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.

    First, create the procedure that declares and then opens a cursor on the titles table:

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'titles_cursor' and type = 'P')
    DROP PROCEDURE titles_cursor
    GO
    CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
    AS
    SET @titles_cursor = CURSOR
    FORWARD_ONLY STATIC FOR
    SELECT *
    FROM titles

    OPEN @titles_cursor
    GO

    Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

    USE pubs
    GO
    DECLARE @MyCursor CURSOR
    EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    FETCH NEXT FROM @MyCursor
    END
    CLOSE @MyCursor
    DEALLOCATE @MyCursor
    GO
    But why do you need to do this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: I couln't return a table from a procedure . Pls Help me

    My 2 cents ...

    A Function can return a table variable but not a procedure. See if you can rewrite the SP as a UDF.

    Originally posted by aneeshattingal
    I couln't return a table from a procedure . Pls Help me

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hey aneeshattingal,

    Just a guess...but you have an Oracle background and are switching to sql server?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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