Save query in temporary table with stored procedures SQL SERVER

Question

Save query in temporary table with stored procedures SQL SERVER

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have a stored procedure:

CREATE PROCEDURE prueba 
@pal varchar(45)
as
CREATE TABLE #param(folio int,tit varchar(max),descr varchar(max))
SET NOCOUNT ON
INSERT INTO #param SELECT * from post where titulo=@pal
SELECT * FROM #param;
go

and so I invoke it

exec prueba @pal="aquamarina"

and I want the result of that select to be saved in the temporary table that I created.

sql-server stored-procedure

asked by Ernesto Emmanuel Yah Lopez 16.06.2017 в 19:29

source

3 answers

I can not access my .js file from my .html file Problems with the UTF-8 encoding in mysql

score 1 · Answer 1

You seem to be looking for this,

insert into TU_TABLA_TEMPRAL exec prueba @pal="aquamarina"

Keep in mind:

The fields that the procedure returns must be the same as your TU_TABLA_TEMPORAL (I mean the same number of columns and the types of data must match)

Greetings!

score 0 · Answer 2

What you try to do I understand what this is:

You have an Sp that creates and fills a temporary table

CREATE PROCEDURE sp_prueba 
AS
    CREATE TABLE #Prueba(id int)
    SET NOCOUNT ON
    INSERT INTO #Prueba (id) VALUES (1)

RETURN 0

Then outside the temporary table you try to recover the data in the following way

EXEC sp_prueba
SELECT * FROM #Prueba;

This should give you an error Invalid object name '#Prueba' because the temporary table only exists within the Sp. Normally this is solved taking the creation of the temporary outside the Sp.

CREATE PROCEDURE sp_prueba 
AS
    SET NOCOUNT ON
    INSERT INTO #Prueba (id) VALUES (1)

RETURN 0

And when invoking you do it like this

CREATE TABLE #Prueba(id int)
EXEC sp_prueba
SELECT * FROM #Prueba;

And now if it should work

score 0 · Answer 3

Hi look, I'm copying a procedure that I had to do where I work with temporary, I hope it serves you, I remain attentive to your doubts. (I put only the part of the query to write the creation code of the sp)

CREATE TABLE #TMPRECOMPRA (ID BIGINT IDENTITY(1,1),facId BIGINT)
            INSERT INTO #TMPRECOMPRA
            SELECT V.FacId 
            FROM FIP_Venta V 
            INNER JOIN FACTURAS F ON F.facID= V.facID 
            WHERE estado = @Estado

                    SELECT   T.facID
                    ,F.estado 'Estado'
                    ,CONVERT(VARCHAR,F.fecha_vencimiento,103) 'fecha_vencimiento'
                    ,CONVERT(NUMERIC(18,0),F.valor_documento)  'valor_rescate'
                    ,DATEDIFF(DAY,GETDATE(),F.fecha_vencimiento) 'Dias_por_vencer'
                    ,ROUND(CONVERT(NUMERIC(18,0),F.valor_documento)-(CONVERT(NUMERIC(18,0),F.valor_documento)/(((v.ven_tasa_captacion/100/30)*(DATEDIFF(DAY,GETDATE(),F.fecha_vencimiento)))+1)),0) 'Interes_por_recuperar'
                    ,CONVERT(NUMERIC(18,0),F.valor_documento) - ROUND(CONVERT(NUMERIC(18,0),F.valor_documento)-(CONVERT(NUMERIC(18,0),F.valor_documento)/(((v.ven_tasa_captacion/100/30)*(DATEDIFF(DAY,GETDATE(),F.fecha_vencimiento)))+1)),0) 'Valor_recompra'
                    ,DATEDIFF(DAY,[dbo].[fnFechaAnteriorHabil](F.fecha_vencimiento), F.fecha_vencimiento)  as 'dias reliq'
                    ,ISNULL(R.Folio_Recompra, '') AS Folio_Recompra
                    FROM #TMPRECOMPRA as T
                    INNER JOIN fip_venta v on v.facID=t.facId
                    INNER JOIN Facturas as F ON t.facId=f.facID
                    LEFT  JOIN  FIP_Recompra R ON T.facID= R.facID
                    WHERE T.ID BETWEEN  CAST(ISNULL(@RegIni, 0) AS VARCHAR)  AND  CAST(ISNULL(@RegFin, 0) AS VARCHAR)



            DROP TABLE #TMPRECOMPRA

        END