March 29, 2004 at 4:45 am
Hi,
I know that it is not advisable to create a temp table based on dynamic sql, however I have a situation wherein I have to create a table with columns as number of days in a month so I am using this small script for accomplishing that task, however I get an error that the temporary table that I am trying to access database from is invalid. I don't if DDL is allowed in Dynamic SQL. Well I have not optimized it yet and this script I used only for checking if the temp table is getting created or not.
Any help would be highly appreciated
CREATE PROCEDURE GETDAYS @MONTH INT, @YEAR INT
AS
DECLARE @CURRENTDATE DATETIME
DECLARE @CURRENTDATE1 DATETIME,
@TABLEDEFQRY NVARCHAR(8000),
@DAYCOUNT INT,
@TABDAYS INT
SET @CURRENTDATE = CAST (CONVERT(VARCHAR(2),@MONTH) +'/'+ '01/' + + CONVERT(VARCHAR(4),@YEAR) AS DATETIME)
SET @CURRENTDATE1 = @CURRENTDATE
SET @DAYCOUNT=1
WHILE MONTH(@CURRENTDATE )= MONTH(CAST(@CURRENTDATE1 AS DATETIME))
BEGIN
SET @CURRENTDATE1=DATEADD(DAY,1,@CURRENTDATE1)
SET @DAYCOUNT=(@DAYCOUNT+1)
END
SET @TABLEDEFQRY = ' CREATE TABLE #DailyOPs (PKEY_DEPT INT,
DEPT_NAME CHAR(20),
TOTAL CHAR(9) DEFAULT(''0''), '
SET @TABDAYS=1
WHILE @TABDAYS <=@DAYCOUNT-2
BEGIN
SET @TABLEDEFQRY =@TABLEDEFQRY +'[' + CONVERT(varchar(11),@CURRENTDATE )+ '] CHAR(9) DEFAULT(''0''), '
SET @CURRENTDATE=DATEADD(DAY,1,@CURRENTDATE)
SET @TABDAYS =@TABDAYS + 1
END
SET @TABLEDEFQRY=@TABLEDEFQRY + '[ ' + CONVERT(VARCHAR(11),@CURRENTDATE) + '] CHAR(9) DEFAULT(''0''))'
EXEC (@TABLEDEFQRY)
SELECT * FROM #DailyOPs
Thanks
Prasad Bhogadi
www.inforaise.com
March 29, 2004 at 5:11 am
Prasad,
I ran your scripts as posted above and received the below errors
Server: Msg 2717, Level 16, State 2, Procedure GETDAYS, Line 0
The size (8000) given to the parameter '@TABLEDEFQRY' exceeds the maximum allowed (4000).
Parameter '@TABLEDEFQRY' has an invalid data type.
At minimum you need to modify your code to make the @TABLEDEFQRY length 4000
Good Luck
Good Hunting!
AJ Ahrens
webmaster@kritter.net
March 29, 2004 at 5:35 am
Choose VARCHAR(8000) instead of NVARCHAR (8000).
March 29, 2004 at 7:25 pm
March 29, 2004 at 11:50 pm
You can not use the temp table after the EXEC. This is because EXEC is run in a new "connection". You could try to create a 'global temporary table' ##DailyOPs, and your proc should work if you solve the NVARCHAR(8000) problem. The NVARCHAR(8000) problem is because nvarchar has a maximum length of 4000 ... What you can do is alter table to alter an existing temp table. This way, you never need the 8000 bytes ... So try this :
CREATE PROCEDURE GETDAYS (@MONTH INT, @YEAR INT )
AS
begin
set nocount on
declare @currentdate datetime
declare @SQL nvarchar(1024)
/* use ##DailyOPs if you need the table outside the proc */
create table #DailyOPs(PKEY_DEPT integer,DEPT_NAME character(20),TOTAL character(9) default('0'))
set @currentdate = right('0000'+convert(varchar(4),@YEAR) ,4)
+ right( '00'+convert(varchar(2),@MONTH),2)
+ '01'
while datepart(month,@currentdate) = @MONTH
begin
set @SQL = 'alter table #DailyOPs add ['
+ convert(varchar(11),@currentdate)
+ '] character(9) default(''0'')'
exec (@SQL)
set @currentdate = dateadd(day,1,@currentdate)
end
select * from #DailyOPs
drop table #DailyOPs
end
go
exec GETDAYS 3,2004
March 30, 2004 at 1:33 am
Sorry for a little confusion, actually I declared @TABLEDEFQRY as Varchar(8000) but since varchar datatype is not supported with EXEC SP_EXECUTESQL I changed the datatype to NVARCHAR(4000), however I missed to change it in the post I made.
Bert your script helps me solve my problem , Thanks a lot.
Prasad Bhogadi
www.inforaise.com
March 30, 2004 at 1:35 am
However I have a question do we need to explicitly drop the temp table, what is the scope of the temp table defined inside a Stored Procedure with Create #TableName syntax.
Thanks
Prasad Bhogadi
www.inforaise.com
March 30, 2004 at 2:03 am
scope is local to the proc and you don't need to explicitly drop the table
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply