April 22, 2009 at 5:25 pm
Hello,
Procedure One throws a temp table with dynamic columns, how can I catch this table into another procedure.
Create Proc p1
param datetime
as
-- Operations to create temp table with dynamic columns
--Operations to Fill temp table
Select * from #temp
go
Create Proc p2
as
¿¿ Insert Into #temp2
exec p1 getdate()?? --this not work, of course.
go
Exec p2
Are there ways or one way to catch the output from proc p1??
I was reading this post, but almost answer my doubt, but not.
http://qa.sqlservercentral.com/Forums/Topic466506-338-1.aspx
http://qa.sqlservercentral.com/Forums/Topic466506-338-1.aspx
thanks in advance.
April 22, 2009 at 5:31 pm
Your problem here is going to be the dynamic columns. If the columns were fixed, then you could use the following:
CREATE TABLE #temp2 (define all the columns here);
INSERT INTO #temp2 (list all columns to be inserted)
EXECUTE dbo.Proc1
But, you won't know what columns are going to be returned from that procedure so the above will not work. That leaves you with the only option of taking the code from proc1 and incorporating it into your new procedure.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 22, 2009 at 8:32 pm
There is another option. Write the output to a table within the stored procedure and pass the name of that table back as an output parameter. Of course, your calling application will have to be able to figure out what columns were dynamically included in the table. That issue is a major reason why they can't make it easy for us to redirect output from stored procedures to temp tables. Any way you go, you have to coordinate the dynamics within and without the stored proc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 7:48 am
ok. thanks both for your answers. but at this moment I think the answer of Bob is more useful for me.
How can I do that?
well, actually the store two is just to send the same info but in html format, for another store that sends emails through sql.
April 23, 2009 at 9:24 am
Once you have the name of the new table passed back you can interrogate system data to find out the names and data types of the columns in it, but now you are back to writing dynamic SQL based on that information. Before we start going through contorted thinking, let me ask you why you want to do this as a nested procedure? Why not simply have the calling procedure execute the dynamic SQL directly? What are you wanting to do with the results? Don't just say "put them in a temporary table". Give us the complete picture and there is probably a much easier solution. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 9:36 am
Actually, Bob's process will not work with a temp table because a temp table created in a procedure would no longer exist when the procedure terminates. You would have to create the temp table in the calling procedure and pass that to the procedure to be populated.
However, you can't do that because you don't know what columns are going to exist before calling the stored procedure.
I agree with Bob - let's take a step back and see what you are trying to accomplish. There is probably a much better way of solving the problem.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 10:04 am
Ok.
I have an App Report, this execute the store p1. here there is no problem.
Beside, i have an store p3, to send an email with those data. p3 define, subject, To, From, Header and receive the Body from store p2.
The store p2, calls p1 and gives the format htm to dynamic table.
this is what I want to do? but accept suggestions.
April 23, 2009 at 10:09 am
That's helpful, thank you. Now what is dynamic about your output? Different selection criteria? Different columns every time? Can you give me one or two quick examples?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 11:39 am
Well, I choose to fix the columns because the matter from the begin was the name headers of columns and the N number of columns has limit that are the 12 months of the year.
This fix so much problems, and I just need to validate the alias of columns.
In the App I can hide the columns without data, the same into store to send email.
Anyway, Thanks a lot.
April 23, 2009 at 2:26 pm
I'm sorry, but I'm having trouble seeing exactly what you mean. Would you please give me an example of a table that would be generated dynamically, with column headings and two rows of data. Something like this:
declare @sample table (item varchar(30), jan int, feb int, mar int, apr int, may int, jun int, jly int, aug int, sep int, oct int, nov int, [dec] int)
insert into @sample
select 'Widget 2345',100,200,300,250,500,600,450,250,375,212,634,578
select * from @sample
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 5:11 pm
Dont worry Man, I change the temp table to defined columns y with that fix my problem.
but if you like to see the code where I create the temp table I show you.
The part where create the temp tbl look like this:
--Create dynamic temp table.
--Declarar var para nombres de columnas variables
Declare @tri1mxp varchar(50),
@tri1usd varchar(50),
@mes1mxp varchar(50),
@mes1usd varchar(50),
@dif1mxp varchar(50),
@dif1usd varchar(50),
@mes2mxp varchar(50),
@mes2usd varchar(50),
@dif2mxp varchar(50),
@dif2usd varchar(50),
@t1mxp varchar(50),
@t1usd varchar(50),
@dif3mxp varchar(50),
@dif3usd varchar(50),
@mes1 datetime,
@mes2 datetime,
@SqlAlter varchar(100),
@sQuery varchar(8000),
@Division varchar(100),
@MontoMXP varchar(100),
@MontoUSD varchar(100)
SET @tri1mxp = ''
SET @tri1usd = ''
CREATE TABLE #REPTRIMESTRE
(
Division varchar(100),
[T-1|MXP] numeric(22,4),
[T-1|USD] numeric(22,4),
[DIF|T-1-Tri|MXP] varchar(30),
[DIF|T-1-Tri|USD] varchar(30)
)
SET @tri1mxp = 'Tri|' + Convert(varchar(10),dbo.fnMesAnio(dbo.fnFechaTrimestre(@TrimestreAnterior,@AnioTrim,1))) + '-' + Convert(varchar(10),dbo.fnMesAnio(dbo.fnFechaTrimestre(@TrimestreAnterior,@AnioTrim,2))) + '|MXP'
SET @tri1usd = 'Tri|' + Convert(varchar(10),dbo.fnMesAnio(dbo.fnFechaTrimestre(@TrimestreAnterior,@AnioTrim,1))) + '-' + Convert(varchar(10),dbo.fnMesAnio(dbo.fnFechaTrimestre(@TrimestreAnterior,@AnioTrim,2))) + '|USD'
Select @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @tri1mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
Select @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '[' +@tri1usd + ']'+ ' numeric(22,4)'
EXEC (@SqlAlter)
IF @DifMes = 3 OR @DifMes = -9 --quiere decir que se completaron 2 meses despues del ultimo mes del trim anterior
BEGIN
SET @mes1 = dbo.fnFechaTrimestre(@TrimestreAnterior,@AnioTrim,2) --sacar fecha ultimo mes del trim ant.
SET @mes1 = DATEADD("month",1, @mes1)
SET @mes2 = DATEADD("month",1, @mes1)
--definir columnas
SET @mes1mxp = Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|MXP'
SET @mes1usd = Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|USD'
SET @dif1mxp = 'DIF|' + Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|MXP'
SET @dif1usd = 'DIF|' + Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|USD'
SET @mes2mxp = Convert(varchar(10), dbo.fnMesAnio(@mes2)) + '|MXP'
SET @mes2usd = Convert(varchar(10), dbo.fnMesAnio(@mes2)) + '|USD'
SET @dif2mxp = 'DIF|' + Convert(varchar(10), dbo.fnMesAnio(@mes2)) + '|MXP'
SET @dif2usd = 'DIF|' + Convert(varchar(10), dbo.fnMesAnio(@mes2)) + '|USD'
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @mes1mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @mes1usd + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @dif1mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @dif1usd + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @mes2mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @mes2usd + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @dif2mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @dif2usd + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
END
IF @DifMes = 2 OR @DifMes = -10
BEGIN
SET @mes1 = dbo.fnFechaTrimestre(@TrimestreAnterior,@AnioTrim,2) --sacar fecha ultimo mes del trim ant.
SET @mes1 = DATEADD("month",1, @mes1)
--definir columnas
SET @mes1mxp = Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|MXP'
SET @mes1usd = Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|USD'
SET @dif1mxp = 'DIF|' + Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|MXP'
SET @dif1usd = 'DIF|' + Convert(varchar(10), dbo.fnMesAnio(@mes1)) + '|USD'
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @mes1mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @mes1usd + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @dif1mxp + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
SELECT @SqlAlter = 'Alter Table #REPTRIMESTRE ADD ' + '['+ @dif1usd + ']' + ' numeric(22,4)'
EXEC (@SqlAlter)
END
--IF @DifMes = 1 OR @DifMes = -11
--no se agrega ninguna columna.
--Inserto los Montos solo para el Trimestre Anterior
SET @sQuery = ''
SET @sQuery = @sQuery + ' INSERT INTO #REPTRIMESTRE (Division,['+ @tri1mxp + '],[' + @tri1usd + ']) ' +
-- SET @sQuery = @sQuery +
' SELECT ISNULL(DO.NombreCorto + '' - '' + DO.Descripcion,''ALL'') [Division Origen], '
-- ''$'' + ' --CONVERT(VARCHAR,CONVERT (MONEY,
+ ' SUM(CASE CO.IdMoneda WHEN ' + CONVERT(varchar,@IdMonedaMXP) + ' THEN I.SALDO ELSE 0 END) ' +
--),1)
+' [MXP - Monto], '
--''$'' + ' -- CONVERT(VARCHAR, CONVERT(MONEY,
+ ' SUM(CASE CO.IdMoneda WHEN ' + CONVERT(varchar,@IdMonedaUSD) + ' THEN I.SALDO ELSE 0 END) ' +
--),1)
+ ' [USD - Monto] ' +
' FROM #TOTAL I INNER JOIN CUENTA CO ON I.IdCuenta = CO.IdCuenta
INNER JOIN Empresa EO ON EO.IdEmpresa = CO.IdEmpresa
INNER JOIN Division DO ON DO.IdDivision = EO.IdDivision
WHERE dbo.fnXRTValidaPermisosEmpresa(CO.IdEmpresa,' + cONVERT(VARCHAR,@IdUsuario)+ ') = 1 '
IF @IdDivision IS NOT NULL
SELECT @sQuery = @sQuery + '
AND EO.IdDivision = ' + Convert(varchar,@IdDivision)
IF @IdEmpresa IS NOT NULL
SELECT @sQuery = @sQuery + '
AND EO.IdEmpresa = ' + Convert(varchar,@IdEmpresa)
SELECT @sQuery = @sQuery + ' AND I.FECHA >= dbo.fnFechaTrimestre(' + CONVERT(VARCHAR,@TrimestreAnterior) +',' + CONVERT(VARCHAR,@AnioTrim) + ',1)
AND I.Fecha <= dbo.fnFechaTrimestre(' + CONVERT(VARCHAR,@TrimestreAnterior) +',' + CONVERT(VARCHAR,@AnioTrim) + ',2)
GROUP BY ISNULL(DO.NombreCorto + '' - '' + DO.Descripcion,''ALL'')
ORDER BY ISNULL(DO.NombreCorto + '' - '' + DO.Descripcion,''ALL'') '
EXEC(@sQuery)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply