p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Book: Professional Microsoft SQL Server 2008 Administration ISBN: 978-0-470-24796-9 (http://p2p.wrox.com/forumdisplay.php?f=426)
-   -   Stored Procedure Quandry (http://p2p.wrox.com/showthread.php?t=73130)

TComfort March 6th, 2009 04:02 AM

Stored Procedure Quandry
 
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

re: Stored Procedure Quandry
 
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 for your help
 
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)




All times are GMT -4. The time now is 04:27 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.