CREATE a Temp Table via "EXEC (@SQL)"

I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:

--------------------------
-- Get all SubquestionIDs for this concept
--------------------------
DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR
SELECT QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
FROM RotationMaster AS RM
INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation
INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber
INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumber
WHERE RM.Study = @.Study
GROUP BY QGDM.SubquestionID,
QGDM.ShortName,
QGDM.PosRespValues
HAVING QGDM.SubquestionID <> 0

--------------------------
-- Dynamically create a Temp Table to store the data, simulating a pivot table
--------------------------
SET @.Count = 2
SET @.SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'

OPEN curStudySubquestions
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SQL = @.SQL + ', Col' + CAST(@.Count AS VARCHAR(5)) + ' VARCHAR(10)'
SET @.Count = @.Count + 1
FETCH NEXT FROM curStudySubquestions INTO @.SubquestionID, @.ShortName, @.PosRespValues
END
SET @.SQL = @.SQL + ', ShowOrder SMALLINT)'

CLOSE curStudySubquestions
PRINT 'Create Table SQL:'
PRINT @.SQL
EXEC (@.SQL)
SET @.ErrNum = @.@.ERROR
IF (@.ErrNum <> 0)
BEGIN
PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!'
RETURN
END
PRINT '*** Table Created ***'

-- Test that the table was created
SELECT *, 'TEST' AS AnyField FROM #AllSubquestions

The line PRINT @.SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):

CREATE TABLE #AllSubquestions
(Col1 VARCHAR(100),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(10),
Col7 VARCHAR(10),
ShowOrder SMALLINT)

However, the SELECT statement to test the creation of the table produces this error:

*** Table Created ***
Server: Msg 208, Level 16, State 1, Procedure
sp_SLIDE_CONCEPT_AllSubquestions, Line 73
Invalid object name '#AllSubquestions'.

It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?The "select *" is outside of the transaction that created the temp table. The way you have written the proc, the temp table disappears at the conclusion of the exec(@.sql).

I would suggest you create a unique static table name (not a temp table) that you feed to the exec sql, then reference the table later as needed. The do the right thing and clean-up after yourself before you exit the sproc.|||The problem is that this temp table will have a variable number of fields when it is run, so a static table won't work. It would be messy to figure out which fields contain data. Oh, and that is only a part of the SP, I clean up properly :)

What I tried was creating "##Allsubquestions", and the table remains in scope for the rest of the SP.

Maybe, though, there is a better way of doing this? Suppose this study has 4 questions, repeating once per Concept. I need the data in this temp table like this:

Col1 Col2 (Question1) Col3 (Question2) Col4 (Question3) Col5 (Question4) ShowOrder
-------------------------------------
Concept 1 25 86 15 47 1
Concept 2 35 89 54 72 2
Concept 3 69 17 48 36 3
Norm A 50 40 40 30 100
Norm B 54 38 42 36 101

This SP will return a recordset to a generic routine that will populate a MS Graph in a PowerPoint presentation, so I need return a dataset that is formatted where I can just load the Graph's Datasheet object from the SP result set. What will change from study to study is the number of questions. This example has four. There may be 8, 10, or any other number.

Counting People in time

I'm trying to count the number of people that I have in a building at any
given hour from a table that basically looks like this...
CREATE TABLE InOutTimes
(
In_Date smalldatetime NOT NULL,
Out_Date datetime NOT NULL
)
go
INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 10:00:00')
INSERT InOutTimes VALUES ('8/1/01 08:00:00', '8/1/01 09:00:00')
INSERT InOutTimes VALUES ('8/1/01 13:00:00', '8/1/01 13:30:00')
INSERT InOutTimes VALUES ('8/1/01 17:00:00', '8/1/01 20:00:00')
INSERT InOutTimes VALUES ('8/2/01 09:00:00', '8/2/01 12:00:00')
INSERT InOutTimes VALUES ('8/2/01 10:00:00', '8/2/01 11:00:00')
INSERT InOutTimes VALUES ('8/2/01 16:00:00', '8/2/01 23:00:00')
INSERT InOutTimes VALUES ('8/2/01 17:00:00', '8/3/01 01:00:00')
I need something that will output something like this...
00 1
01 1
02 0
03 0
04 0
05 0
06 0
07 0
08 2
09 3
10 3
11 2
12 1
13 1
14 0
15 0
16 1
17 3
18 3
19 3
20 3
21 2
22 2
23 2
I have the 0 - 23 hour in memory that I can update, but my issue is when the
in/out times span a day. Is there a way I can do all this without a cursor
and case statements?
ANY help is appreciated.
Thanks
Scott
BTW - Wish me luck with the hurricane!!Hello, Scott
Try this:
CREATE TABLE #Numbers (N int PRIMARY KEY)
INSERT INTO #Numbers
SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 23
SELECT N as TheHour, (
SELECT COUNT(*) FROM InOutTimes
WHERE DATEDIFF(d,In_Date,Out_Date)=0
AND N BETWEEN DATEPART(hour,In_Date) AND DATEPART(hour,Out_Date)
OR DATEDIFF(d,In_Date,Out_Date)=1
AND (N>=DATEPART(hour,In_Date) OR N<=DATEPART(hour,Out_Date))
OR DATEDIFF(d,In_Date,Out_Date)>1
) AS Cnt
FROM #Numbers ORDER BY N
Razvan
PS. Good luck with the hurricane! You are in Lafayette LA, right?|||That look like just what I needed! Awesome! Many thanks. I'll post in a
few days and let you know if it works out in the report :)
Yeah, Lafayette. They are expecting it to hit about 70 miles West of us so
I'm hauling to Atlanta in a bit. Have good one!
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1127407645.880484.6580@.g14g2000cwa.googlegroups.com...
> Hello, Scott
> Try this:
> CREATE TABLE #Numbers (N int PRIMARY KEY)
> INSERT INTO #Numbers
> SELECT DISTINCT number FROM master..spt_values
> WHERE number BETWEEN 0 AND 23
> SELECT N as TheHour, (
> SELECT COUNT(*) FROM InOutTimes
> WHERE DATEDIFF(d,In_Date,Out_Date)=0
> AND N BETWEEN DATEPART(hour,In_Date) AND DATEPART(hour,Out_Date)
> OR DATEDIFF(d,In_Date,Out_Date)=1
> AND (N>=DATEPART(hour,In_Date) OR N<=DATEPART(hour,Out_Date))
> OR DATEDIFF(d,In_Date,Out_Date)>1
> ) AS Cnt
> FROM #Numbers ORDER BY N
> Razvan
> PS. Good luck with the hurricane! You are in Lafayette LA, right?
>

Count(IIF) not working

Hello, I have been fighting with getting the following statement to work
=Count(IIF( Fields!PDDAYS.Value>30,1,0))
I basically have returned all records to get a total count and need to weed
out those I want so I can divide the [totalofallrecords] by the
[totalofthose>30daysdue]
Any help would be appreciated.
SonjaI suppose you really want the Sum() aggregate, rather than Count(). Count
works the same way as it works in SQL - every value different than NULL will
be counted.
Try this (inside a data region - e.g. in a table header/footer etc.):
=Sum(IIF( Fields!PDDAYS.Value>30,1,0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"SLB" <SLB@.discussions.microsoft.com> wrote in message
news:1D0DCEDA-882C-41E5-9FB7-3308E91DF828@.microsoft.com...
> Hello, I have been fighting with getting the following statement to work
> =Count(IIF( Fields!PDDAYS.Value>30,1,0))
> I basically have returned all records to get a total count and need to
> weed
> out those I want so I can divide the [totalofallrecords] by the
> [totalofthose>30daysdue]
> Any help would be appreciated.
> Sonja|||Thanks so much, worked like a charm. Don't know what I was thinking.
"Robert Bruckner [MSFT]" wrote:
> I suppose you really want the Sum() aggregate, rather than Count(). Count
> works the same way as it works in SQL - every value different than NULL will
> be counted.
> Try this (inside a data region - e.g. in a table header/footer etc.):
> =Sum(IIF( Fields!PDDAYS.Value>30,1,0))
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "SLB" <SLB@.discussions.microsoft.com> wrote in message
> news:1D0DCEDA-882C-41E5-9FB7-3308E91DF828@.microsoft.com...
> > Hello, I have been fighting with getting the following statement to work
> >
> > =Count(IIF( Fields!PDDAYS.Value>30,1,0))
> >
> > I basically have returned all records to get a total count and need to
> > weed
> > out those I want so I can divide the [totalofallrecords] by the
> > [totalofthose>30daysdue]
> >
> > Any help would be appreciated.
> > Sonja
>
>