create proc usp_debug1 @lname varchar(40) as declare @au_id varchar(11) declare @au_fname varchar(20) declare @au_lname varchar(40) declare authors cursor for select au_id, au_fname, au_lname from pubs.dbo.authors order by au_lname open authors fetch next from authors into @au_id, @au_fname, @au_lname while @@fetch_status = 0 begin if @au_lname >= @lname begin exec usp_debug2 @au_id break end else fetch next from authors into @au_id, @au_fname, @au_lname end close authors deallocate authors declare authors cursor for select au_id, au_fname, au_lname from pubs.dbo.authors order by au_lname open authors close authors deallocate authors go create proc usp_debug2 @au_id varchar(11) as declare @x int select 'Updating Contract on Author: ' + dbo.udf_debug3(au_fname) + ' ' + au_lname from pubs.dbo.authors where au_id = @au_id set @x = 0 update pubs.dbo.authors set contract = @x where au_id = @au_id select 'Update complete' select contract from pubs.dbo.authors where au_id = @au_id go -- This function accepts a sting and upper cases the first two character of each word. CREATE FUNCTION udf_debug3 (@string varchar(100)) RETURNS varchar(100) AS BEGIN DECLARE @substring1 varchar(100), @cursor int, @localstring varchar(100) SET @cursor = CHARINDEX(' ', @string) IF @cursor > 0 BEGIN set @string=@string SET @localstring = SUBSTRING (@string, @cursor + 1, Len(@string) - @cursor + 1) SET @substring1 = dbo.udfFormat_FirstCharUpperCase ( @localstring) SET @string = upper(substring(@string,1,2)) + lower(substring(@string,3,len(@string))) SET @string = LEFT(@string, @cursor) + @substring1 END ELSE BEGIN SET @string = upper(substring(@string,1,2)) + lower(substring(@string,3,len(@string))) END return (@string) END