February 7, 2002 at 7:43 am
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
February 7, 2002 at 7:56 am
What error are you getting?
The code create a global temporary table without issue on my system.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 12:34 pm
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
February 7, 2002 at 12:59 pm
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.
February 7, 2002 at 1:27 pm
Are you running this through Query Analyzer or some other client (say a VB program)?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 7, 2002 at 1:38 pm
The key is how did you test it and are you sure it works outside a procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply