May 16, 2012 at 2:20 pm
I know this is weird, just couldn't think of a possible reason, please help. Thanks.
I have a sp, which will create a temp table and the temp table's columns are generated dynamically from another table's content. the code piece is here:
...............
Declare @sqlCreateTable varchar(2000)
declare @Category Varchar(255)
declare @CategoryChecked bit --1: the DocID's this category is Checked; 0: not checked
Set @sqlCreateTable = 'Create table #tFinal (DocID int'
--print @sqlCreateTable
DECLARE c CURSOR FOR
SELECT Category From Categories
OPEN c
FETCH NEXT FROM c INTO @Category
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sqlCreateTable = @sqlCreateTable + ', [' + @Category + '] varchar (50)'
--print @sqlCreateTable
FETCH NEXT FROM c INTO @Category
END
CLOSE c
DEALLOCATE c
Set @sqlCreateTable = @sqlCreateTable + ')'
--Now the create table query is ready, exec it to create the table
print @sqlCreateTable
exec (@sqlCreateTable)
select * from #tFinal
.....................................
The table #tFinal cannot be created, I double checked the dynamic sql (@sqlCreateTable) that is executed to create it, there is no any problem with it, and to confirm this, I print it out, and then copy the print output which is a create table query, I run it in another window, the table DOES get created!!!
Then why it DOES NOT get created inside the sp? There is no any error output!
Thanks.
May 16, 2012 at 2:45 pm
halifaxdal (5/16/2012)
I know this is weird, just couldn't think of a possible reason, please help. Thanks.I have a sp, which will create a temp table and the temp table's columns are generated dynamically from another table's content. the code piece is here:
...............
Declare @sqlCreateTable varchar(2000)
declare @Category Varchar(255)
declare @CategoryChecked bit --1: the DocID's this category is Checked; 0: not checked
Set @sqlCreateTable = 'Create table #tFinal (DocID int'
--print @sqlCreateTable
DECLARE c CURSOR FOR
SELECT Category From Categories
OPEN c
FETCH NEXT FROM c INTO @Category
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sqlCreateTable = @sqlCreateTable + ', [' + @Category + '] varchar (50)'
--print @sqlCreateTable
FETCH NEXT FROM c INTO @Category
END
CLOSE c
DEALLOCATE c
Set @sqlCreateTable = @sqlCreateTable + ')'
--Now the create table query is ready, exec it to create the table
print @sqlCreateTable
exec (@sqlCreateTable)
select * from #tFinal
.....................................
The table #tFinal cannot be created, I double checked the dynamic sql (@sqlCreateTable) that is executed to create it, there is no any problem with it, and to confirm this, I print it out, and then copy the print output which is a create table query, I run it in another window, the table DOES get created!!!
Then why it DOES NOT get created inside the sp? There is no any error output!
Thanks.
The table is created, it is created within the context of the EXEC statement. When that returns, the temporary table is then deleted. You either need to create a permanent table in your database or use a global temporary table (## instead of #) but then you need to be sure to delete either when you are finished with it.
May 16, 2012 at 2:51 pm
I have made the sp very simple to isolate the problem, here is the whole sp:
ALTER proc [dbo].[spDashboardByCategories1]
as
Declare @sqlCreateTable varchar(2000)
Set @sqlCreateTable = 'Create table #tFinal (DocID int, [Unmasked Production Data in a Non-Production Environment] varchar (50), [Mixed Mode Authentication] varchar (50), [Password Standards] varchar (50), [Software Currency] varchar (50), [Insecure Deployment of Powerful Privileges] varchar (50), [Audit Logs ] varchar (50), [FTP ] varchar (50), [File permissions] varchar (50), [Secure disposal ] varchar (50), [Encryption] varchar (50), [Patch Management] varchar (50), [Firewall Standards] varchar (50), [ABM] varchar (50), [Citrix] varchar (50), [Server Hardening ] varchar (50))'
--Now the table is ready
print @sqlCreateTable
exec (@sqlCreateTable)
print 'create table query executed'
select * from #tFinal
When I run the sp, I can see the 'create table query executed' printed which means exec the @sqlCreateTable is not a problem.
But, the table #tFinal is not created!
Did I do anything wrong? Please help.......any clue is appreciated.........
May 16, 2012 at 2:52 pm
Lynn Pettis (5/16/2012)
The table is created, it is created within the context of the EXEC statement. When that returns, the temporary table is then deleted. You either need to create a permanent table in your database or use a global temporary table (## instead of #) but then you need to be sure to delete either when you are finished with it.
OK, I will try again, thanks.
May 16, 2012 at 2:54 pm
Big thanks, that's the reason
May 16, 2012 at 2:55 pm
Lynn Pettis (5/16/2012)
halifaxdal (5/16/2012)
I know this is weird, just couldn't think of a possible reason, please help. Thanks.I have a sp, which will create a temp table and the temp table's columns are generated dynamically from another table's content. the code piece is here:
...............
Declare @sqlCreateTable varchar(2000)
declare @Category Varchar(255)
declare @CategoryChecked bit --1: the DocID's this category is Checked; 0: not checked
Set @sqlCreateTable = 'Create table #tFinal (DocID int'
--print @sqlCreateTable
DECLARE c CURSOR FOR
SELECT Category From Categories
OPEN c
FETCH NEXT FROM c INTO @Category
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sqlCreateTable = @sqlCreateTable + ', [' + @Category + '] varchar (50)'
--print @sqlCreateTable
FETCH NEXT FROM c INTO @Category
END
CLOSE c
DEALLOCATE c
Set @sqlCreateTable = @sqlCreateTable + ')'
--Now the create table query is ready, exec it to create the table
print @sqlCreateTable
exec (@sqlCreateTable)
select * from #tFinal
.....................................
The table #tFinal cannot be created, I double checked the dynamic sql (@sqlCreateTable) that is executed to create it, there is no any problem with it, and to confirm this, I print it out, and then copy the print output which is a create table query, I run it in another window, the table DOES get created!!!
Then why it DOES NOT get created inside the sp? There is no any error output!
Thanks.
The table is created, it is created within the context of the EXEC statement. When that returns, the temporary table is then deleted. You either need to create a permanent table in your database or use a global temporary table (## instead of #) but then you need to be sure to delete either when you are finished with it.
The table WAS created, during the context of the EXEC statement. Once that finished, the table was dropped by SQL Server as it is a local temporary table.
Run the following:
...............
Declare @sqlCreateTable varchar(2000)
declare @Category Varchar(255)
declare @CategoryChecked bit --1: the DocID's this category is Checked; 0: not checked
Set @sqlCreateTable = 'Create table #tFinal (DocID int'
--print @sqlCreateTable
DECLARE c CURSOR FOR
SELECT Category From Categories
OPEN c
FETCH NEXT FROM c INTO @Category
WHILE @@FETCH_STATUS = 0
BEGIN
Set @sqlCreateTable = @sqlCreateTable + ', [' + @Category + '] varchar (50)'
--print @sqlCreateTable
FETCH NEXT FROM c INTO @Category
END
CLOSE c
DEALLOCATE c
Set @sqlCreateTable = @sqlCreateTable + '); select * from #tFinal;'
--Now the create table query is ready, exec it to create the table
print @sqlCreateTable
exec (@sqlCreateTable)
--select * from #tFinal
.....................................
May 17, 2012 at 10:16 am
Thanks Lynn,
I need to use the temp table for further data processing so defining it as global is the only way.
May 17, 2012 at 10:56 am
halifaxdal (5/17/2012)
Thanks Lynn,I need to use the temp table for further data processing so defining it as global is the only way.
Just remember that using a global tempory table has it own set of issues. The main one being that only one instance of the procedure can run at a time. Second, you may want to be sure to drop the table when you are done with it.
May 18, 2012 at 3:44 am
halifaxdal, I believe you could also create the start of the temp table outside of the dynamic sql stmt with just the DocId column. Then change your dynamic sql stmt to be an ALTER TABLE stmt which will add the columns to the existing temp table.
May 18, 2012 at 4:10 am
halifaxdal (5/17/2012)
I need to use the temp table for further data processing so defining it as global is the only way.
Lynn has explained you clearly why the temp table is not created. However you can create the temp table initially & then add columns to it later dynamically (as another post suggests above). It could be done like this:
--************************ The category table ********************************
CREATE TABLE Categories
(
Category VARCHAR(50)
)
GO
INSERT INTO Categories VALUES ('Cat1')
INSERT INTO Categories VALUES ('Cat2')
INSERT INTO Categories VALUES ('Cat3')
GO
--************************ Creating temp table and adding column to that ********************************
Declare @sqlCreateTable varchar(2000)
declare @Category Varchar(255)
declare @CategoryChecked bit --1: the DocID's this category is Checked; 0: not checked
IF OBJECT_ID('tempdb..#TFinal') IS NOT NULL
BEGIN
DROP TABLE #TFinal
END
CREATE TABLE #TFinal (DocId INT)
DECLARE @AlterCommands VARCHAR(MAX)
SET @AlterCommands = ''
SELECT @AlterCommands = @AlterCommands + 'ALTER TABLE #TFinal ADD ['+Category +'] VARCHAR(50);' FROM Categories
EXEC (@AlterCommands)
GO
SELECT * FROM #TFinal
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply