error message that has me stumped. Usually a PL/SQL user and not sure what to do. the SP is below the error message I'm getting is must declare @max_date, it is so I'm stumped
Any help would be greatly appreciated.
Thanks in advance!!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc IBU_ODS_CLEANUP @ODS_NAME VARCHAR(30)
as
declare @update_table varchar(75), @session_name varchar(75), @sql_stmt varchar(300), @max_date varchar(50)
declare parameterlist cursor for
select UPDATE_TABLE, SESSION_NAME FROM ODS_PARAMETERS WHERE UPDATE_TABLE IS NOT NULL AND ODS_NAME = @ODS_NAME
--update last lpa execution date
OPEN parameterlist
FETCH NEXT FROM parameterlist
into @update_table, @session_name
while @@FETCH_STATUS = 0
begin
set @sql_stmt = 'SELECT @max_date = convert(varchar(50),MAX(TRANSACTION_DATE),21)
FROM ' + @update_table
print(@sql_stmt)
exec(@sql_stmt)
print (@max_date)
if @max_date is not null
begin
set @sql_stmt = 'update ODS_PARAMETERS set PARAMETER_VALUE = '''+ @max_date +
''' WHERE SESSION_NAME = ''' + @session_name + ''''
PRINT (@sql_stmt)
end
FETCH NEXT FROM parameterlist
into @update_table, @session_name
end
CLOSE parameterlist
DEALLOCATE parameterlist
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Any help would be greatly appreciated.
Thanks in advance!!
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc IBU_ODS_CLEANUP @ODS_NAME VARCHAR(30)
as
declare @update_table varchar(75), @session_name varchar(75), @sql_stmt varchar(300), @max_date varchar(50)
declare parameterlist cursor for
select UPDATE_TABLE, SESSION_NAME FROM ODS_PARAMETERS WHERE UPDATE_TABLE IS NOT NULL AND ODS_NAME = @ODS_NAME
--update last lpa execution date
OPEN parameterlist
FETCH NEXT FROM parameterlist
into @update_table, @session_name
while @@FETCH_STATUS = 0
begin
set @sql_stmt = 'SELECT @max_date = convert(varchar(50),MAX(TRANSACTION_DATE),21)
FROM ' + @update_table
print(@sql_stmt)
exec(@sql_stmt)
print (@max_date)
if @max_date is not null
begin
set @sql_stmt = 'update ODS_PARAMETERS set PARAMETER_VALUE = '''+ @max_date +
''' WHERE SESSION_NAME = ''' + @session_name + ''''
PRINT (@sql_stmt)
end
FETCH NEXT FROM parameterlist
into @update_table, @session_name
end
CLOSE parameterlist
DEALLOCATE parameterlist
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO