CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
ale
ale

Reputation: 3421

Return Table from stored procedure

I have this stored procedure that call other second stored procedure which returns a table with 5 columns. I insert the results into the temporary table in the first stored procedure. The idea is to show this table in a Crystal Report, so I need this stored procedure to return the temporary table.

How can I make it to return the table with the 5 columns and the values??

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[paBltBuscarBoletasASA] @id_Asa int  
      -- Add the parameters for the stored procedure here
AS
DECLARE @Query int
CREATE TABLE #tablaTemporal (Numero_Pregunta varchar, Numero_Boleta int, Cultivo varchar, Numero_Siembra int, Detalle_Error varchar)
DECLARE miCursor CURSOR FOR

                SELECT 
                    localizacion.c_Fk_IdBoleta
                FROM 
                    Blt_Boleta as boleta, Fnc_Localizacion as localizacion
                WHERE 
                    boleta.c_Pk_IdBoleta = localizacion.c_Fk_IdBoleta AND
                    localizacion.si_CodAsa = @id_Asa
OPEN miCursor
FETCH NEXT FROM miCursor INTO @Query

WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO #tablaTemporal(Numero_Pregunta, Numero_Boleta, Cultivo, Numero_Siembra, Detalle_Error) exec dbo.paBltMarcarErroresBoleta @Query

    FETCH NEXT FROM miCursor INTO @Query
END

CLOSE miCursor
DEALLOCATE miCursor

Upvotes: 1

Views: 2729

Answers (2)

xQbert
xQbert

Reputation: 35323

Why does it have to be temporary?

Can the table data simply be refreshed each time? Then you don't run into the issue of the temp table being destroyed each time it falls out of scope/context.

Upvotes: 0

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74340

Well the most obvious solution is to place at the bottom of the stored proc:

SELECT Numero_Pregunta, Numero_Boleta, Cultivo, Numero_Siembra, Detalle_Error
FROM   #tablaTemporal

This will return the data to the stored proc caller.

Upvotes: 3

Related Questions