Dynamic Table Creation from Stored Procedure
-
Hi,
I have a proc that produces an output with a varying amount of columns (depending on the parameters passed).
What I would like to do is create a table (doesn't matter if it's a temp) based on the output of this procedure. I cannot create the table before I run the proc because the quantity of columns could be different each time the proc is run.
Does anybody know if this is possible.
-
Create the 'stub' of a temp table (say, just an identity column) with a standard name before calling the stored proc. Inside the stored proc you could add the columns you need and populate the table. Presumably you build the query string in the sp, so you could build the 'alter table' string at the same time. Because the table was not created inside the stroed proc, it will not be dropped when the sp completes, and will still be visible to the calling process.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
stax68 - thank you, could you show me what you mean please?
-
Use the SELECT...INTO...FROM... syntax, e.g.:
SELECT <select list from your query here>
INTO ##TempTable
FROM <rest of your query here>--Jonathan
--Jonathan -
OK still can't get it to work. Here is a simular example of what I'm doing using the Northwind database.
This is the proc I'm executing
exec crosstab2 'select top 20 shipvia from orders group by shipvia','count(shipvia)','year(orderdate)','orders','shipvia'
Here is the output
shipvia199619971998199619971998199619971998
3581227558122755812275
2561531175615311756153117
1381337838133783813378
Now the years will obviously inccrease so I 'don't want to keep tweaking the proc.
-
quote:
OK still can't get it to work. Here is a simular example of what I'm doing using the Northwind database.This is the proc I'm executing
exec crosstab2 'select top 20 shipvia from orders group by shipvia','count(shipvia)','year(orderdate)','orders','shipvia'
Here is the output
shipvia199619971998199619971998199619971998
3581227558122755812275
2561531175615311756153117
1381337838133783813378
Now the years will obviously inccrease so I 'don't want to keep tweaking the proc.
Rewrite your stored procedure to select into the (global temporary?) table instead of just returning the result set.
--Jonathan
--Jonathan -
Try...
-- Not recommended to use in a loop, does take a little longer to execute
-- Generic example ...
select * from OpenRowset('SQLOLEDB',
'Server=(local);Trusted_Connection=yes',
'Exec Master.dbo.sp_help ')
-- Example with your call (you need to replace [DataBaseName])
select * from OpenRowset('SQLOLEDB',
'Server=(local);Trusted_Connection=yes',
'Exec [DataBaseName].dbo.crosstab2
''select top 20 shipvia from orders group by shipvia'',
''count(shipvia)'', ''year(orderdate)'', ''orders'', ''shipvia'' ')
Once you understand the BITs, all the pieces come together
-
Not a good solution but if the process time is small you could change the proc to have additional parameters, one to return the number of years and another to request the resultset or not. Then you could call the proc requesting the number of years, create the temp table, then call again to get resultset into the temp table.
Ah! Just seen the flaw
, you want the years. OK, one parameter to request list of years or the data.Edited by - davidburrows on 11/19/2003 06:38:09 AM
Far away is close at hand in the images of elsewhere.
Anon. -
-- You have two options that I can think of:
-- 1) Users use the pivottable functionality built into olap services (i.e. in excel its a snap) and just provide them a sp to call that returns non-pivot result set.
-- 2) Use string executes
--
-- My opinion: SQL has a limit on the number of fields you can have. There is also a limit on the number
-- of columns a user can actually interpret into something useful- typically the simpler a report the better.
-- For example, if they want to see beyond a year of data, they likely want it aggregated down by quarter
-- or by year. This realistically limits the number of columns you will really need to output.
--
-- Beyond that, it is likely custom work will have to be done anyhow. I typically just use columns named
-- by their sequence and tell them what the starting period is, or have the procedure pass back the starting period.
-- If the need for extensive analysis data is great, its good to set them up with excel with its pivot table utility
-- and a read-only copy of the data (like movelog or something). Outside of that, it is an OLAP project, which
-- is an animal all its own.
--
-- If SQL itself just HAS to give it specific column names instead of just a sequence number, you can do something like I
-- did below. Run it in the northwind db.
--
-- This example uses temp tables. I would think you could set one up that only used the sum(case when..) for the fields
-- and build a string to execute that may have less of a footprint.
set nocount on
drop table #x
go
drop table #y
go
declare @SQLStr varchar(8000)
select
count(*) as OrderCount
,ShipVia
,year(OrderDate) as OrderDateYear
into #x
from
Orders
group by
ShipVia
,year(OrderDate)
order by
ShipVia
,year(OrderDate)
create table #y (ShipVia int not null)
-- while this can be done inside the data pivot loop by checking MVs, this is clearer since it's only an example.
declare cFieldsToAdd insensitive cursor
for
select distinct 'alter table #y add ODY'+ltrim(str(OrderDateYear))+' int null' from #x
for read only
open cFieldsToAdd
fetch next from cFieldsToAdd into @SQLStr
while @@fetch_status = 0
begin
execute(@SQLStr)
fetch next from cFieldsToAdd into @SQLStr
end
close cFieldsToAdd
deallocate cFieldsToAdd
declare
@cOrderDateYear int
,@cOrderCount int
,@cShipVia int
,@LastShipVia int
set @LastShipVia = -1
-- fill the pivot table
declare cDataToPivot insensitive cursor
for
select * from #x order by ShipVia, OrderDateYear
for read only
open cDataToPivot
fetch next from cDataToPivot into @cOrderCount, @cShipVia, @cOrderDateYear
while @@Fetch_Status = 0
begin
if @LastShipVia <> @cShipVia
set @SQLStr = 'insert into #y (ShipVia, ODY'+ltrim(str(@cOrderDateYear))+') values ('+ltrim(str(@cShipVia))+', '+ltrim(str(@cOrderCount))+')'
else
set @SQLStr = 'update #y set ODY'+ltrim(str(@cOrderDateYear))+' = '+ltrim(str(@cOrderCount))+' where ShipVia = '+ltrim(str(@cShipVia))
execute(@SQLStr)
set @LastShipVia = @cShipVia
fetch next from cDataToPivot into @cOrderCount, @cShipVia, @cOrderDateYear
end
close cDataToPivot
deallocate cDataToPivot
select * from #y order by ShipVia
-
Sorry for the formatting. I hate it when forum interfaces remove all my formatting tabs. Unfortunately I am too used to tab instead of space formatting to change in isql.
Is there a way to make it keep my tab formatting?
-
quote:
Hi,I have a proc that produces an output with a varying amount of columns (depending on the parameters passed).
What I would like to do is create a table (doesn't matter if it's a temp) based on the output of this procedure. I cannot create the table before I run the proc because the quantity of columns could be different each time the proc is run.
Does anybody know if this is possible.
You may like to use System Stored Procedure Called sp_executesql
and pass your parameters the complete help you can get from Help online Good Luck -
I may be a bit off beam on this one however, having read so many requests for a CROSSTAB (TRANSFORM/PIVOT) function in SQL Server, I wondered if a procedure I wrote (sp_XTAB) might be of benefit to some.
It produces crosstab views with a (current) capacity of up to (around) 100 columns depending upon the size of the query generated from x7 parameters.
EXEC sp_XTAB 'db_name', 'name_for_output_view', 'source_table' ,'output_field' ,'pivot_field' ,'calc_field' ,'calc'
No need for special tables or anything else. I guess the same PRINCIPAL could be used to generate tables, if required. sp_STAB???
Wish I'd seen your code sample before I wrote mine as there are some similarities. I think the main difference is that you have scoped by one level while I used two.
This is my first sight of Server Central and I am most impressed.
Regards,
Ian
, you want the years. OK, one parameter to request list of years or the data.
and pass your parameters the complete help you can get from Help online Good LuckViewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply