SQL Server: Cursor example

Oh, look also an example of getting the 'Return' value from a Stored procedure that is called via dynamic SQL

DECLARE @sql NVARCHAR(4000);
DECLARE	@Result int
DECLARE @RunID NVARCHAR(100) = cast(getDate() as NVARCHAR(100));

DECLARE @ISectName nvarchar(255);
DECLARE @ISectSeq int;
DECLARE @IStepName nvarchar(255);
DECLARE @IStepSeq int;
DECLARE @TStepName nvarchar(255);
DECLARE @TStepSeq int;
DECLARE @TStepSP nvarchar(255);

DECLARE db_cursor CURSOR FOR 
	select 
		 ISect.name as ISectName, ISect.sequence as ISectSeq
		,IStep.name as IStepName, IStep.sequence as IStepSeq
		,ts.name as TStepName, ts.sequence as TStepSeq, ts.storedProcedure as TStepSP
	from dbo.ImplementationSection ISect
	join ImplementationStep IStep on IStep.implementationSection = ISect.id
	join TestStep ts on ts.implementationStep = IStep.id
	order by ISect.sequence, IStep.sequence, ts.sequence
	
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO
	 @ISectName,	@ISectSeq
	,@IStepName,	@IStepSeq
	,@TStepName,	@TStepSeq,	@TStepSP
WHILE @@FETCH_STATUS = 0  
    BEGIN  
		set @sql = 'exec @return_value = dbo.' + @TStepSP
				+ ' @runID = N''' + @RunID + ''',
				@implementationSection = N'''+ @IStepName + ''',
				@implementationStep = N'''+ @IStepName + ''''
				
				PRINT @sql
				exec sp_executesql @sql
				,N'@return_value int OUTPUT'
				,@return_value = @Result OUTPUT
				
				-- Do something with Results
				Select 		 @ISectName as ISectName
							,@ISectSeq as ISectSeq
							,@IStepName as IStepName
							,@IStepSeq as IStepSeq
							,@TStepName as TStepName
							,@TStepSeq as TStepSeq
							,@TStepSP as TStepSP
							,@Result as RESULT

		FETCH NEXT FROM db_cursor INTO
			 @ISectName, @ISectSeq
			,@IStepName,	@IStepSeq
			,@TStepName, @TStepSeq, @TStepSP
	END  

CLOSE db_cursor  
DEALLOCATE db_cursor

 

    begin transaction

    declare @year int, @oid int, @rowcount int, @identity int, @code varchar(200), @versionNote varchar(1000), @affectedItems int, @draftOid int
    select @year = 2011

    create table #versionedOids (oid int)
    
    DECLARE db_cursor CURSOR FOR
    -- Returns the oid of listcontrols that are associated with items that have a restriction
    -- expiry of the current year -1 and the list is not set to DO_NOT_CHANGE but is APPROVED.
    select distinct lc.oid, lc.code from gladis.listcontrol  AS LC
        join gladis.item it on lc.oid=it.listcontroloid
        INNER JOIN (SELECT     MAX(VERSIONNUMBER) AS MAXV, CODE AS CODEV
                        FROM          gladis.LISTCONTROL
                        GROUP BY CODE) AS MAXVERS ON LC.CODE = MAXVERS.CODEV
                        AND LC.VERSIONNUMBER = MAXVERS.MAXV
        where lc.RESTRICTIONAUTOEXPIRY <> 'DO_NOT_CHANGE'
            and lc.REVIEWSTATE = 'APPROVED'
            and it.RESTRICTIONEXPIRYYEAR = (@year)

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @oid, @code

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

        -- make sure we've got items in this list that will be affected
        -- before we go ahead and version it
        select @affectedItems = count(it.oid)
        from gladis.item it
        join gladis.listcontrol lc on it.listcontroloid=lc.oid
        where listcontroloid=@oid
        AND (
            (restrictionautoexpiry = 'CHANGE_TO_O'    and restrictionstatus = 'RESTRICTED') OR
            (restrictionautoexpiry = 'CHANGE_TO_M'    and restrictionstatus = 'RESTRICTED') OR
            (restrictionautoexpiry = 'CHANGE_TO_O'    and restrictionstatus = 'MAY_BE_RESTRICTED')
        )
        and restrictionexpiryyear=@year

        if @affectedItems > 0
        begin
            -- version the list controls that we're going to update
            begin try
                exec gladis.List_VersionList @oid, 'Automated Restriction Status Expiry','A_AND_D', 1, 'System', 'System', null,null,0,1
            end try
            begin catch
                begin transaction
                    insert into SQLAUDIT (message, updated)
                    values ('Tasks_PublicViewExpiry. Error versioning list with oid '+ cast(@oid as varchar(10)), getdate())
                commit                              
            end catch
                
            -- note the oid we just versioned from
            insert into #versionedOids (oid) values (@oid)

            -- do we have a draft version that needs to be shifted out?
            select @draftOid = oid from gladis.listcontrol where PREVIOUSVERSIONOID = @oid and REVIEWSTATE <> 'APPROVED' and REVIEWSTATE <> 'PROVISIONALLY_APPROVED'
            
            if @draftOid > 0
            begin
                
                -- shift notes against draft down one
                update gladis.listcontrolversionnote
                set versionnumber = versionnumber-1
                where entityoid = @draftOid
                    and versionnumber = (select versionnumber from gladis.listcontrol where oid=@draftOid)

                -- shift the existing draft down one
                update gladis.listcontrol
                set versionnumber = (select min(versionnumber) from gladis.listcontrol AS LC where lc.code = @code) - 1,
                previousversionoid = (select top 1 lc.oid from gladis.listcontrol AS LC where lc.code = @code order by versionnumber desc)
                where oid = @draftOid
    
                select @draftOid = 0
                
            end
            
        end

        FETCH NEXT FROM db_cursor INTO @oid, @code
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    create table #temp (itemoid int)

    declare @versionNumber int

    DECLARE db_cursor CURSOR FOR
    -- Returns the approved lists we've just versioned
    select distinct lc.oid, lc.versionnumber from gladis.listcontrol  AS LC
        where lc.previousversionoid in (select oid from #versionedOids)

    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @oid, @versionNumber

    WHILE @@FETCH_STATUS = 0  
    BEGIN  

        select @versionNote = 'Versioned by Restriction Expiry Process<br/>'

        insert into #temp
        select it.oid
        from gladis.item it
        join gladis.listcontrol lc on it.listcontroloid=lc.oid
        where listcontroloid=@oid and restrictionautoexpiry = 'CHANGE_TO_O'
        and restrictionstatus = 'RESTRICTED'
        and restrictionexpiryyear=@year

        update gladis.item set restrictionstatus = 'OPEN'
        where oid in (select itemoid from #temp)
        
        select @rowcount = @@rowcount
        
        if @rowcount > 0
        begin
            insert into gladis.listcontrolaudit (entityoid, createddate, author, office,event,eventid)    
            values (@oid, getdate(), 'System', 'System', cast(@rowcount as varchar(10)) + ' items updated from R to O by restriction expiry process', 'ITEMSCHANGED')
        
            select @versionNote = @versionNote + cast(@rowcount as varchar(10)) + ' items updated from R to O by restriction expiry process<br/>'

            select @identity = @@identity

            insert into gladis.ITEMAUDITRELATIONSHIP (listcontrolauditoid, itemoid)
            select @identity, itemoid from #temp
        end
        
        truncate table #temp
        
        insert into #temp
        select it.oid
        from gladis.item it
        join gladis.listcontrol lc on it.listcontroloid=lc.oid
        where listcontroloid=@oid and restrictionautoexpiry = 'CHANGE_TO_M'
        and restrictionstatus = 'RESTRICTED'
        and restrictionexpiryyear=@year
    
        update gladis.item set restrictionstatus = 'MAY_BE_RESTRICTED'
        where oid in (select itemoid from #temp)

        select @rowcount = @@rowcount

        if @rowcount > 0
        begin
            insert into gladis.listcontrolaudit (entityoid, createddate, author, office,event,eventid)    
            values (@oid, getdate(), 'System', 'System', cast(@rowcount as varchar(10)) + ' items updated from R to M by restriction expiry process', 'ITEMSCHANGED')
        
            select @versionNote = @versionNote + cast(@rowcount as varchar(10)) + ' items updated from R to M by restriction expiry process<br/>'

            select @identity = @@identity
            
            insert into gladis.ITEMAUDITRELATIONSHIP (listcontrolauditoid, itemoid)
            select @identity, itemoid
            from #temp
        end

        truncate table #temp
        
        insert into #temp
        select it.oid
        from gladis.item it
        join gladis.listcontrol lc on it.listcontroloid=lc.oid
        where listcontroloid=@oid and restrictionautoexpiry = 'CHANGE_TO_O'
        and restrictionstatus = 'MAY_BE_RESTRICTED'
        and restrictionexpiryyear=@year
        
        update gladis.item set restrictionstatus = 'OPEN'
        where oid in (select itemoid from #temp)

        select @rowcount = @@rowcount

        if @rowcount > 0
        begin
            insert into gladis.listcontrolaudit (entityoid, createddate, author, office,event,eventid)    
            values (@oid, getdate(), 'System', 'System', cast(@rowcount as varchar(10)) + ' items updated from M to O by restriction expiry process', 'ITEMSCHANGED')
            
            select @versionNote = @versionNote + cast(@rowcount as varchar(10)) + ' items updated from M to O by restriction expiry process<br/>'

            select @identity = @@identity
            
            insert into gladis.ITEMAUDITRELATIONSHIP (listcontrolauditoid, itemoid)
            select @identity, itemoid from #temp
        end

        truncate table #temp

        -- put in version note for changes that have happened
        insert into gladis.listcontrolversionnote
            (entityoid, versionnumber, author, createddate, versionnotes)
        values
            (@oid, @versionNumber, 'System', cast(GETDATE() as datetime), @versionNote)

        -- copy into shifted draft if available
        select @draftOid = oid from gladis.listcontrol where previousversionoid = @oid and REVIEWSTATE <> 'APPROVED' and REVIEWSTATE <> 'PROVISIONALLY_APPROVED'
        
        if @draftOid > 0
        begin
            
            insert into gladis.listcontrolversionnote
                (entityoid, versionnumber, author, createddate, versionnotes)
            values
                (@draftOid, @versionNumber, 'System', cast(GETDATE() as datetime), @versionNote)
                
            select @draftOid = 0
        end
        
        FETCH NEXT FROM db_cursor INTO @oid, @versionNumber
    END  

    CLOSE db_cursor  
    DEALLOCATE db_cursor

    truncate table #versionedOids
    
    commit transaction

 

use xxxxx
set nocount on

declare @oid int
declare @itemCode varchar(40)
declare @listOid int
declare @commentIndex int
declare @series int
declare @title nvarchar(max)
declare @oldTitle nvarchar(max)

declare @url nvarchar(255)

create table #affectedItems(oid int, series int, oldtitle nvarchar(max), title nvarchar(max), pid varchar(20))

declare @cursor cursor
set @cursor  = cursor
for select l.oid, i.oid, i.code, i.title, i.seriesactualcode from gladis.item  i
inner join gladis.listcontrol l
on i.listcontroloid=l.oid
open @cursor

fetch next from @cursor into @listOid, @oid, @itemCode, @title, @series

while @@fetch_status=0
begin

    -- Determine if Digital Archive link present in title

    if (@series=6539)
    begin
        select @commentIndex = charindex('<p>', @title)
    end
    else
        select @commentIndex = charindex('<!--', @title)
    

    if (@commentIndex > 0)
    begin
        select @url = substring(@title, @commentIndex, len(@title))    
        select @oldTitle = @title
        select @title = substring(@title, 0, @commentIndex)

        -- Strip HTML prior to PID
        select @url = substring(@url, patindex('%archives:[0-9]%', @url), len(@url))

        -- Strip HTML after PID
        if (@series = 8220 or @series = 8344)
        begin
            -- Format is archives:12345&download=true
            select @url = substring(@url, 0, patindex('%&%', @url))
        end    
        else
            -- Format is archives:12345">
            select @url = substring(@url, 0, patindex('%"%', @url))

        insert into #affectedItems(oid, series, oldtitle, title, pid)
        values(@oid, @series, @oldTitle, @title, @url)
        
        -- Update title description
        update gladis.item set title = @title where oid=@oid
        update archwaypublic.archwaypublic.publicitem set name=@title where oid=@oid

        if (@itemCode != 21010450)
        begin
            -- Set pid
            update gladis.item set digitalpid = @url where oid=@oid
            update archwaypublic.archwaypublic.publicitem set digitalpid=@url where oid=@oid

            -- Insert List audit event
            insert into dbo.appauditlog(entityoid, entityname, event, userid, eventdesc, occurreddt, outcome, severity,
                msg, originatingoffice, commonname) values (@listOid, 'List', 'UPDATE', 'System', 'Digital Archive Link Removal',
                getdate(), 4, 0, 'Link to Digital Archive extracted from item R' + @itemCode + ' title', 'System', 'System')
        end
    end

    -- TODO update item proper
    -- TODO add audit event
    fetch next from @cursor into @listOid, @oid, @itemCode, @title, @series
end
close @cursor
deallocate @cursor

select * from #affectedItems
drop table #affectedItems