Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 528

From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'log'
And p.name = 'LogStartOfApplication')
begin
print 'Dropping log.LogStartOfApplication stored
procedure'
Drop Procedure [log].LogStartOfApplication
print 'Log.LogStartOfApplication stored procedure
dropped'
end
print 'Creating log.LogStartOfApplication stored
procedure'
go
Create Procedure [log].LogStartOfApplication
@ApplicationName varchar(255)
As
declare @ErrMsg varchar(255)
declare @AppID int = (Select ApplicationID
From cfg.Applications
Where ApplicationName
= @ApplicationName)
If (@AppID Is Null)
begin
set @ErrMsg = 'Cannot find ApplicationName '
+ Coalesce(@ApplicationName, '<NULL>')
raiserror(@ErrMsg,16,1)
return-1
end
Insert Into [log].SSISAppInstance
(ApplicationID, StartDateTime, Status)
Output inserted.AppInstanceID
Values