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.

CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?

We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.

We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.

In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.

Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.

Counting rows by a dynamic SQL statements

I need to count number of record return by a SQL statement. Any idea to do
this?
This is the sample SQL. You can try in Northwind database.
The subquery inside COUNT() expression actually is a dynamic sql which I may
change it anytime. My primary purpose is to get number of record after I
executed a dynamic SQL statement. The query below defintely cannot work
because COUNT() doesn;t take subquery.
At first, I try to use @.@.rowcount which return the number of record. Besides
using @.@.rowcount, any other T-SQL can be used?
DECLARE @.cnt int
SELECT @.cnt=COUNT(
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
)You can specify your SQL query as a derived table and use sp_executesql to
return the count variable as an output parameter. This will return the
count without the accompanying data:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT @.cnt = COUNT(*)
FROM (
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
) AS t
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Use a similar technique to get both the resultset and count:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
SET @.cnt = @.@.ROWCOUNT
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Leong" <ch_leong@.hotmail.com> wrote in message
news:ehYf%23NOHFHA.3944@.TK2MSFTNGP10.phx.gbl...
>I need to count number of record return by a SQL statement. Any idea to do
>this?
> This is the sample SQL. You can try in Northwind database.
> The subquery inside COUNT() expression actually is a dynamic sql which I
> may change it anytime. My primary purpose is to get number of record after
> I executed a dynamic SQL statement. The query below defintely cannot work
> because COUNT() doesn;t take subquery.
> At first, I try to use @.@.rowcount which return the number of record.
> Besides using @.@.rowcount, any other T-SQL can be used?
>
> DECLARE @.cnt int
> SELECT @.cnt=COUNT(
> SELECT Customers.ContactName
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
> WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
> GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
> Customers.City, Customers.Country
> HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
> )
>