A personal repository of random information in compensation for a fatigued biological computer
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