Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
gdeconto
Posting Yak Master
107 Posts |
Posted - 2003-04-30 : 11:55:59
|
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
547 Posts |
Posted - 2003-04-30 : 16:24:51
|
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 = 1WHILE @tblNum < 1000 --some large number to try the loop up toBEGINSET @tblName = 'tempdb..##' + ltrim(str(@tblnum))IF OBJECT_ID(@name) IS NULL --this table name isn't used yetBREAKELSESET @tblNum = @tblNum + 1--Here have the code for:1. If @tblNum = 1000, we broke b/c we exceeded the highest number2. create the temp table with the latest value in @tblName Sarah Berger MCSD |
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2003-04-30 : 16:31:08
|
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 uglywhile I got around the limitation, would still be nice to be able to get the crosstab sproc to use temp tables. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-30 : 20:14:57
|
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. |
|
|
gdeconto
Posting Yak Master
107 Posts |
Posted - 2003-05-01 : 00:04:38
|
Thx for the info Rob.Appreciate the help and the original crosstab code. |
|
|
|
|
|
|
|