Wrox Programmer Forums
|
Book: Professional Microsoft SQL Server 2008 Administration ISBN: 978-0-470-24796-9
This is the forum to discuss the Wrox book Professional Microsoft SQL Server 2008 Administration by Brian Knight, Ketan Patel, Wayne Snyder, Ross LoForte, Steven Wort; ISBN: 9780470247969
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Book: Professional Microsoft SQL Server 2008 Administration ISBN: 978-0-470-24796-9 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 6th, 2009, 04:02 AM
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default 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.
 
Old March 6th, 2009, 04:57 PM
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default re: Stored Procedure Quandry

Since I posted the question I can tell myself:

Try using INSERT.
 
Old March 7th, 2009, 04:50 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
The Following User Says Thank You to Old Pedant For This Useful Post:
TComfort (March 9th, 2009)
 
Old March 9th, 2009, 01:12 PM
Registered User
 
Join Date: Mar 2009
Posts: 5
Thanks: 2
Thanked 0 Times in 0 Posts
Default 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)






Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored procedure ashuphp MySQL 1 April 2nd, 2007 04:20 PM
stored procedure keyvanjan Classic ASP Basics 6 August 1st, 2006 07:42 AM
stored procedure kvanchi ADO.NET 1 December 9th, 2004 07:27 AM
Stored Procedure to.... flyin ADO.NET 3 March 3rd, 2004 02:01 AM
Query Quandry Ben Access 2 February 10th, 2004 06:30 AM





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