Can't work out whats wrong with my declare

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can't work out whats wrong with my declare

Author

Topic

Newbie01
Starting Member

6 Posts

Posted - 2007-12-20 : 20:11:23

Hi,

Back ground:
I've got 2 tables that I'm working with
1. NEMTIME
- Time in 30min increments (01/01/2004 - 31/12/2007)
(NEMTIME datetime primary key)
2. Contract Master
- Contracts with a SINGLE start date & a SINGLE end date (usually 3 months in duration)
(Start_date datetime,
End_date datetime,
Volume int,
Cpty_code varchar(10),
Product_code varchar(10))

3. #temp
- Holds the distinct contract counterparty names.
(MyKey int identity,
cpcode varchar(10))

Summation:
I need to create tables which house the sum of the half hourly (FROM NEMTIME) contract VOLUME for each PRODUCT_CODE traded (VOlUME and PRODUCT_CODE from Contract Master) with the Cpty_code.

Code thus far:
/* Creating a table of distinct Counter Party codes */
use Master_contracts
if object_id('tempdb..#temp') is not null drop table #temp
go

create table #temp
(
MyKey int identity primary key,
cpcode varchar(10)
)
insert into #temp
select distinct cpty_code from [contract master]

I've come across my first problem in the next step which is to create the tables based on distinct cpty_code 's. My problem is I've had to hard code the Product_Code, I'll explain in a second:

/* Creating the Counter Party HH tables */
declare @TableName nchar(10)
declare TName cursor for
select cpcode from #temp
OPEN TName
FETCH NEXT FROM TName
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @SQL VarChar(1000)
SELECT @SQL = 'Create Table ' + 'CP' + @TableName + '('
SELECT @SQL = @SQL + 'NEMTIME datetime, FUTCover int, [Fut-C] int, [Fut-P] int, [Fut-Cap] int)'
Exec (@SQL)
FETCH NEXT FROM TName
INTO @TableName
END
CLOSE TName
DEALLOCATE TName

You can see that I've tried to tack on the product_code after my select @sql statement (FUTCover int, [Fut-C] int, [Fut-P] int, [Fut-Cap] int). The problem being that not every Cpty_code has all of these product codes. I need help finding a way to dynamically insert product_code's relevant to only that particular cpty_code which the current table belongs to... if that makes sence.

My next problem comes from trying to get the half hourly data in to the tables that i've created in the previous step:

declare @TableName nchar(10) --
declare TName cursor for --Declaring cursor variables
select cpcode from #temp --
declare @theTime datetime -- the current interval to assess
declare @maxTime datetime -- maximum date interval in nemtime

select @theTime = min(start_date) from [contract master] where cpty_code = @TableName
select @maxTime = max(end_date) from [contract master] where cpty_code = @TableName
-- while the current time is less than the latest date
while @theTime <= @maxTime
begin
-- cover is the name of the 2nd column in the nemtime table
Open TName
Fetch NEXT from TName
Into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
update @TableName
-- set the total volume to the sum between the start and end dates
set NEMTIME = NEMTIME, FUTCover = (select sum(volume) from [Contract Master]
where @theTime >= start_date and @theTime <= end_date)
where nemtime = @theTime
-- add 30 minutes to obtain the next interval and repeat
select @theTime = dateadd(mi, 30, @theTime)
end
FETCH NEXT FROM TName
INTO @TableName
END
CLOSE TName
DEALLOCATE TName

I'm getting these errors:

Msg 137, Level 15, State 2, Line 21
Must declare the variable '@TableName'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'where'.

This doesn't make sense to me, mainly because I'm a newb at SQL, but also because I thought that I declared @tablename earlier in the statement... and the second error message I'm stumped with.

If you've made it this far I really would appreciate your help here.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-21 : 13:12:24

The problem, at lesat the one you are asking about, is that you cannot use a variable in that way. For example:

-- This does not work
DECLARE @TableName VARCHAR(50)
SET @TableName = 'MyTable'

UPDATE @Tablename
SET Foo = 1

Assuming you cannot do this set based, you would have to change this code to be Dynamic SQL, like you are doing in the first cursor example you provided.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 13:27:45

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

Newbie01
Starting Member

6 Posts

Posted - 2008-01-01 : 18:56:33

Thanks for that.

I could really use your help again.

When I exec the following:
declare @TableName nchar(10)
declare @start_date datetime
declare @end_date datetime
declare TName cursor for
select cpcode from #temp
OPEN TName
FETCH NEXT FROM TName
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @SQL VarChar(1000)
SELECT @SQL = 'insert into ' + 'CP' + @TableName + '('
SELECT @SQL = @SQL +
'set' + @start_date + '= (select min(start_date) from [contract master] where cpty_code = ' + @TableName + ')' +
'set' + @end_date + '= (select max(end_date) from [contract master] where cpty_code = ' + @TableName + ')' +
'SELECT NEMTIME
FROM NEMTIME
where NEMTIME >' + @start_date + 'and NEMTIME <' + @end_date + ')'
Exec (@SQL)
FETCH NEXT FROM TName
INTO @TableName
END
CLOSE TName
DEALLOCATE TName

I get this error message:
Msg 241, Level 16, State 1, Line 13
Syntax error converting datetime from character string.

Does anyone know where I'm going wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-01 : 20:13:32

change min(start_date)
to
convert(varchar(10), min(start_date), 121)

and also similarly for the max(end_date)

actually you can combine the min(start_date) and max(end_date) into the same query


select min(start_date), max(start_date)
from   [contract master] 
where . . .

KH
[spoiler]Time is always against us[/spoiler]

Newbie01
Starting Member

6 Posts

Posted - 2008-01-01 : 21:55:58

I'm still geting this error message:
Msg 241, Level 16, State 1, Line 13
Syntax error converting datetime from character string.

After I exec this code (which is the above with the covert statement added):

declare @TableName nchar(10)
declare @start_date datetime
declare @end_date datetime
declare TName cursor for
select cpcode from #temp
OPEN TName
FETCH NEXT FROM TName
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @SQL VarChar(1000)
SELECT @SQL = 'insert into ' + 'CP' + @TableName + '('
SELECT @SQL = @SQL +
'set' + @start_date + '= (select convert(varchar(10), min(start_date), 121) from [contract master] where cpty_code = ' + @TableName + ')' +
'set' + @end_date + '= (select convert(varchar(10), max(end_date), 121) from [contract master] where cpty_code = ' + @TableName + ')' +
'SELECT NEMTIME
FROM NEMTIME
where NEMTIME >' + @start_date + 'and NEMTIME <' + @end_date + ')'
Exec (@SQL)
FETCH NEXT FROM TName
INTO @TableName
END
CLOSE TName
DEALLOCATE TName

Any other ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-01 : 22:03:50

your insert statement is wrong.

Put a "print @SQL" before the exec(@SQL) and inspect the output there.

KH
[spoiler]Time is always against us[/spoiler]

Newbie01
Starting Member

6 Posts

Posted - 2008-01-01 : 22:16:05

It still returns the same error message:
Msg 241, Level 16, State 1, Line 15
Syntax error converting datetime from character string.

Any other suggestions?

Newbie01
Starting Member

6 Posts

Posted - 2008-01-01 : 22:17:02

...with no results from the 'Print @SQL'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-01 : 22:24:11