I coulnt return a table from a procedure . Pls Help me

I couln't return a table from a procedure . Pls Help meCan you send the procedure ? Would help|||I didn't think (again?) you could...

I tried

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?|||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|||Hey aneeshattingal,

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