trying my first MS stored procedure and get...

[Terris]

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

 

[gmmastros]

Your problem is that when you exec a dynamic sql string, the data is not passed back (into the @maxdate variable) the way you expect it to.

You could probably get this to work without having to use a cursor. I suggest that you try to clearly explain what you expect this stored procedure to do, and someone may be able to help you eliminate the cursor.

I have 1200 stored procedures in my database and only 1/2 a dozen of them use a cursor. You'll be glad to be rid of it, trust me.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

sp_executesql can do what you are trying to do. The full syntax for sp_executesql is available in books online.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Terris]

The suggestion was to explain exactly what I am trying to do.
I am looping through a control table that stores work table names.
from these 'work' tables I am pulling the max date stored and updating another control table that keeps track of oldest date transfered from transactional to datawarehouse environment. The @max_date variable is to hold the oldest date processed which will then be put into the control table.
You'll notice several print statements in my stored procedure this is just for testing purposes.