December 9, 2003 at 10:58 am
I am running SQL Server 2000 (sp3A) on a Microsoft Server 2003. I am trying to pivot entries in one table, writing to a second table.
declare @cnt int
declare @division float
declare @district float
declare @FacName nvarchar(255)
declare @date_ datetime
declare @flow real
declare @storagewater real
declare @source nvarchar(255)
declare @meas_devic nvarchar(255)
declare @old_ditch nvarchar(255)
set @cnt=1
open tmp_cursor
Fetch next from tmp_cursor into @division,@district,@FacName,@date_,
@flow,@storagewater,@source,@meas_devic
set @old_ditch=@FacName
While @@fetch_status = 0
Begin
if @old_ditch<>@Facname
Begin
set @old_ditch = @FacName
set @cnt=1
End
******
set @sql='update pivot '
+'set date_'+cast(@cnt as varchar(3))+'=@date_,'
+'flow'+cast(@cnt as varchar(3))+'=@flow,'
+'storagewater'+cast(@cnt as varchar(3))+'=@storagewater '
+'where pivot.division=@division and pivot.district=@district and '
+'pivot.FacName=@FacName'
print @sql
exec (@sql)
set @cnt=@cnt+1
Fetch next from tmp_cursor into @division,@district,@FacName,@date_,@flow,@storagewater,@source,@meas_devic
End
go
This section of the code doesn't work. The message I get is
update pivot set date_1=@date_,flow1=@flow,storagewater1=@storagewater where pivot.division=@division and pivot.district=@district and pivot.FacName=@FacName
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@date_'.
But the prior code looping correctly, but will not write entries into the second table, because of the "Must declare variable '@date_'.
Any help/ideas would be appreciated
Action
*****
Close tmp_cursor
Deallocate tmp_cursor
December 9, 2003 at 11:04 am
set @sql='update pivot '
+'set date_'+cast(@cnt as varchar(3))+'=@date+'_,flow'+cast(@cnt as varchar(3))+'=@flow,'
+'storagewater'+cast(@cnt as varchar(3))+'=@storagewater '
+'where pivot.division=@division and pivot.district=@district and '
+'pivot.FacName=@FacName'
December 10, 2003 at 8:40 am
I think the issue is that, within the environment of your EXECUTE statement, the variables don't exist.
Try:
set @sql='update pivot '
+'set date_'+cast(@cnt as varchar(3))+'=''' + CONVERT(varchar,@date_) + ''','
+'flow'+cast(@cnt as varchar(3))+'=' + CONVERT(varchar,@flow) + ','
+'storagewater'+cast(@cnt as varchar(3))+'=' + CONVERT(varchar,@storagewater) + ' '
+'where pivot.division=' + CONVERT(varchar,@division) + ' and pivot.district=' CONVERT(varchar,@district) + ' and '
+'pivot.FacName=''' + @FacName + ''''
R David Francis
R David Francis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply