Returning values from temporary table in stored procedure

[Rod Scoullar]

I'm trying to return rows from a stored procedure which includes a temporary
table.

The stored procedure returns the expected rows when executed through SQL
Query Analyser.

On execution in the design page of an .adp project I receive a message "The
stored procedure executed successfully but did not return any records."

The stored procedure has the form.

CREATE/ALTER PROCEDURE ProcedureName
(
@Parameter int
}
AS

SELECT Fields
INTO #TempTable
FROM Table
/* the actual query is more complex */

SELECT Fields FROM #TempTable

RETURN

Any suggestions would be most appreciated.

Rod Scoullar.

 

[Steve Jorgensen]

Try adding SET NOCOUNT ON as the first statement after AS in your procedure
script. Without that, either ADO or DAO will see the rowcount returned from the
first select, and think the procedure has completed before a result is ready.
You should also add SET NOCOUNT ON to a procedure that executes an update query
of some kind and returns no rows, or you won't be able to trap error information
properly at the front-end.

 

[Rod Scoullar]

Thanks Steve,

Your suggestion worked as I'm sure you knew it would.

Interestingly no columns were returned when executed in design view,
although the correct number of rows appeared. When executed as a stored
procedure the correct values were returned.

Thanks again for your time, and the speed with which your answer came.

Rod Scoullar.