SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 question re robvolks awesome pivot table code		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gdeconto
Posting Yak Master

Canada
107 Posts
Posted - 04/30/2003 :  11:55:59  Show Profile  Reply with Quote
robvolks crosstab/pivot sproc is a pretty cool.

question:

I am trying to use the pivot sproc (aka crosstab) in another sproc. In that other sproc, I want to create a list of the info to be pivoted, then pivot that info into a temp table, and then return the pivoted info to the user who called the sproc.

in http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=16225 rob mentions that you can store the results in a table by adding a simple 'INTO xxx'

however, this doesnt work for situations where there are multiple users running the code as it creates a table in the database (ie multiple users trying to create/use the same table).

This doesnt seem to work because temp tables created via EXEC are not in the same scope as the transaction/sproc.

So, is there any way for the pivot results to get stored in a temporary table??

Anyone have some ideas??

simondeutsch
Aged Yak Warrior

USA
547 Posts
Posted - 04/30/2003 :  16:24:51  Show Profile  Reply with Quote
An idea:
Create global temporary tables, which are in the same scope.
Name the tables with numbers, e.g. ##1.
Before you create the temporary table, get the next available number by running a loop to see which one doesn't exist.

DECLARE @tblNum INT,@TblName varchar(25)
SET @tblnum = 1
WHILE @tblNum < 1000 --some large number to try the loop up to
BEGIN
SET @tblName = 'tempdb..##' + ltrim(str(@tblnum))
IF OBJECT_ID(@name) IS NULL --this table name isn't used yet
BREAK
ELSE
SET @tblNum = @tblNum + 1

--Here have the code for:
1. If @tblNum = 1000, we broke b/c we exceeded the highest number
2. create the temp table with the latest value in @tblName


Sarah Berger MCSD
Go to Top of Page

gdeconto
Posting Yak Master

Canada
107 Posts
Posted - 04/30/2003 :  16:31:08  Show Profile  Reply with Quote
thought about that but not sure I like global temp tables in general (kinda like having global variables in your code).

did a quick and dirty hack to create a table dynamically(ie creating the field names based on what the crosstab sproc would return) using a cursor but its a bit ugly

while I got around the limitation, would still be nice to be able to get the crosstab sproc to use temp tables.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 04/30/2003 :  20:14:57  Show Profile  Visit robvolk's Homepage  Reply with Quote
The way to use regular temp tables it is to have the ENTIRE procedure executed as dynamic SQL. That eliminates the scope problems that required the global temp tables. Unfortunately, it also makes assembling the SQL a lot more difficult.

Check the comments section on the article, there are several variations on the code that were posted, one of them will probably do what you want, or point you in the right direction.
Go to Top of Page

gdeconto
Posting Yak Master

Canada
107 Posts
Posted - 05/01/2003 :  00:04:38  Show Profile  Reply with Quote
Thx for the info Rob.

Appreciate the help and the original crosstab code.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000