Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 6th, 2009, 03: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.
  #2 (permalink)  
Old March 6th, 2009, 03: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.
  #3 (permalink)  
Old March 7th, 2009, 03:50 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
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)
  #4 (permalink)  
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)

 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 06:27 AM
Stored Procedure to.... flyin ADO.NET 3 March 3rd, 2004 01:01 AM
Query Quandry Ben Access 2 February 10th, 2004 05:30 AM



All times are GMT -4. The time now is 02:08 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.