Stored Procedure Quandry

TComfort · March 6th, 2009, 04:02 AM

Here's my problem:
I wrote a stored procedure that builds a big ole honking "SELECT, FROM, PIVOT" procedure, and then EXECUTEs it. It works great (and I can vary the output from 1 column to several hundred columns (depending on the series of dates worth of data that I need)).
I get the results in the output window. But, I need to CREATE a table (with the variable columns) from that output. I'm in way over my head and I know I got lucky when I made this procedure work. Is anyone interested in giving it a look? Thanks in advance.

TComfort · March 6th, 2009, 04:57 PM

Since I posted the question I can tell myself:

Try using INSERT.

Old Pedant · March 7th, 2009, 04:50 PM

ummm...INSERT requires that the table you are INSERTing into already exists. So you'd have to figure out how to run a CREATE TABLE first.

What about just using SELECT INTO ???

The basic format is

SELECT list, of, fields
INTO brandNewTableName
FROM ....

If you can just manage to work "INTO xxxx" into the right place in your existing SQL, you might be done.

TComfort · March 9th, 2009, 01:12 PM

Thanks, I got INTO into the right place. Here's the final code:

Code:

ALTER PROCEDURE

Code:

[dbo].[uspBuildShipsByDate]
@datToday date,
@intDaysBack int
AS
SET NOCOUNT ON
DECLARE @datBegin date;
DECLARE @datTheDate date;
DECLARE @intTheDay int;
DECLARE @vchProc varchar(4096);
DECLARE @Error int;
SET @datBegin = DATEADD(day, (@intDaysBack * -1), @datToday)
IF EXISTS (SELECT * FROM sys.objects
WHERE OBJECT_NAME(object_id) = 'tblShips'
AND SCHEMA_NAME(schema_id) = 'dbo' 
AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
DROP TABLE dbo.tblShips

 
SET @vchProc = 'SELECT USER_PART_NO '
SET @datTheDate = @datBegin
SET @intTheDay = @intDaysBack
 
WHILE @datTheDate < @datToday
BEGIN
SET @vchProc = @vchProc + ', [' + CONVERT(varchar,@datTheDate,101) + ']'
SET @vchProc = @vchProc + ' AS [Date-' + CONVERT(varchar, @intTheDay) + ']'
SET @datTheDate = DATEADD(day,1,@datTheDate)
SET @intTheDay = @intTheDay - 1
END
 
SET @vchProc = @vchProc + ', [' + CONVERT(varchar,@datTheDate,101) + ']'
SET @vchProc = @vchProc + ' AS [Date]'
SET @vchProc = @vchProc + ' INTO tblShips' 
SET @vchProc = @vchProc + ' FROM (Select User_part_no, Reqstd_dt, QTY ' 
SET @vchProc = @vchProc + 'FROM vwShips) as SourceTable '
SET @vchProc = @vchProc + 'PIVOT (SUM (QTY) FOR REQSTD_DT IN (' 
SET @datTheDate = @datBegin
 
WHILE @datTheDate < @datToday
BEGIN
SET @vchProc = @vchProc + ' [' + CONVERT(varchar,@datTheDate,101) + '], '
SET @datTheDate = DATEADD(day,1,@datTheDate)
END
 
SET @vchProc = @vchProc + '[' + + CONVERT(varchar,@datTheDate,101) + '] '
SET @vchProc = @vchProc + ')) as pvt '
SET @vchProc = @vchProc + 'ORDER by USER_PART_NO'
EXEC (@vchProc)