Dynamic SQL and Temp table creation

Question

Post reply

Dynamic SQL and Temp table creation

kiranpchandran

Ten Centuries

Points: 1010
More actions
February 7, 2002 at 7:43 am

#101076

Hai all,
Sample code compiling properly, but no work.
Help.....
by
Kiran
create procedure x
as
begin
declare @tmpName nvarchar(100)
set @tmpName =cast( dateName(dd,getDate())+dateName(mm,getDate())+dateName(yy,getDate())+dateName(hh,getDate())+dateName(mi,getDate())+dateName(ss,getDate())+dateName(ms,getDate()) as nvarchar)
declare @sql nvarchar(1000)
set @sql= N'create table ##tmpData' + @tmpName + N'(AccountCode int,varName varchar(30),decOpBal decimal(18,2),TransBal decimal(18,2))'
exec ( @sql)
end

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 1

What error are you getting?

The code create a global temporary table without issue on my system.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

kiranpchandran Ten Centuries Points: 1010 More actions · Answer 2

Hi Brian,

Thank you for your reply,

While executing the procedure I am getting the message

“The Command(s) competed successfully”

But the global temp table is not created!..

If you are using the code listed below instead of previous code, the temp table ##tmpData123456 has been created successfully. I would like to clarify one more doubt. Is there any method for handling the error while using dynamic sql?.

Thanks in advance

Kiran

create procedure x

as

begin

declare @tmpName nvarchar(100)

set @tmpName =N’123456’

declare @sql nvarchar(1000)

set @sql= N'create table ##tmpData' + @tmpName + N'(AccountCode int,varName varchar(30),decOpBal decimal(18,2),TransBal decimal(18,2))'

exec ( @sql)

end

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Have you tried doing PRINT @sql and a PRINT @tmpName instead of EXEC to make sure the text is being handle. If @tmpName is getting set to NULL then if you concatinate to another string it will generate as NULL. Make sure you values are what you expect.

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 4

Are you running this through Query Analyzer or some other client (say a VB program)?

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 5

The key is how did you test it and are you sure it works outside a procedure.

Dynamic SQL and Temp table creation

Cookies on SQLServerCentral