Need debugging help--stored procedure to move database objects

hi all--i'm adapting stored procedure work on transferring sql server 2005 tables , other objects owned under dbo schema on database to another non-dbo schema.  given that, i'm trying use "if...else" prevent dbo-owned stored procedures , tables dtproperties moving rest of objects non-dbo schema.  here's code:

if exists (select * from sys.objects where object_id = object_id(n'[dbo].[chobjowner]') and objectproperty(object_id, n'isprocedure') = 1)

drop procedure [dbo].[chobjowner]

go

set quoted_identifier off set ansi_nulls on

go

create proc chobjowner( @usrname varchar(20), @newusrname varchar(50))

as

-- @usrname current user

-- @newusrname new user

set nocount on

declare @uid int -- uid of user

declare @objname varchar(50) -- object name owned user

declare @currobjname varchar(50) -- checks existing object owned new user

declare @outstr varchar(256) -- sql command 'alter schema', 'alter authorization'

set @uid = user_id(@usrname)

declare chobjownercur cursor static

for

select name from sys.objects where schema_id = @uid

open chobjownercur

if @@cursor_rows = 0

begin

print 'error: no objects owned ' + @usrname

close chobjownercur

deallocate chobjownercur

return 1

end

fetch next from chobjownercur into @objname

while @@fetch_status = 0

begin

set @currobjname = @newusrname + "." + @objname

if (object_id(@currobjname) > 0)

print 'warning *** ' + @currobjname + ' exists ***'

if @currobjname in ('dt_addtosourcecontrol','dt_addtosourcecontrol_u','dt_adduserobject','dt_adduserobject_vcs','dt_checkinobject','dt_checkinobject_u','dt_checkoutobject','dt_checkoutobject_u','dt_displayoaerror','dt_displayoaerror_u','dt_droppropertiesbyid','dt_dropuserobjectbyid','dt_generateansiname','dt_getobjwithprop','dt_getobjwithprop_u','dt_getpropertiesbyid','dt_getpropertiesbyid_u','dt_getpropertiesbyid_vcs','dt_getpropertiesbyid_vcs_u','dt_isundersourcecontrol','dt_isundersourcecontrol_u','dt_removefromsourcecontrol','dt_setpropertybyid','dt_setpropertybyid_u','dt_validateloginparams','dt_validateloginparams_u','dt_vcsenabled','dt_verstamp006','dt_verstamp007','dt_whocheckedout','dt_whocheckedout_u','dtproperties')

begin

print 'warning *** ' + @currobjname + ' system procedure'

end

else

begin

set @outstr = "alter authorization on " + @usrname + "." + @objname + " " + @newusrname

print @outstr

set @outstr = "alter schema " + @newusrname + " transfer " + @usrname + "." + @objname

print @outstr

print 'go'

fetch next from chobjownercur into @objname

end

close chobjownercur

deallocate chobjownercur

set nocount off

return 0

go

set quoted_identifier off

set ansi_nulls on

go

 

here's error get:

msg 102, level 15, state 1, procedure chobjowner, line 51

incorrect syntax near '0'.

i know cursor having problem closing , deallocating, not sure why error happening @ point.  ideas?

thanks!

one of 'begin' s has no 'end'.

SQL Server  >  SQL Server Tools