Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.com
Try using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handl...ml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com
sql