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

/* rpt schema */
If Not Exists(Select name
From sys.schemas
Where name = 'rpt')
begin
print 'Creating rpt schema'
declare @sql varchar(100) = 'Create Schema rpt'
exec(@sql)
print 'Rpt schema created'
end
Else
print 'Rpt schema already exists.'
print ''
/* rpt.ReturnAppInstanceHeader stored procedure */
If Exists(Select s.name + '.' + p.name
From sys.procedures p
Join sys.schemas s
On s.schema_id = p.schema_id
Where s.name = 'rpt'
And p.name = 'ReturnAppInstanceHeader')
begin
print 'Dropping rpt.ReturnAppInstanceHeader stored
procedure'
Drop Procedure rpt.ReturnAppInstanceHeader
print 'Rpt.ReturnAppInstanceHeader stored procedure
dropped'
end
print 'Creating rpt.ReturnAppInstanceHeader stored
procedure'
go
Create Procedure rpt.ReturnAppInstanceHeader
@ApplicationName varchar(255) = NULL
As
Select a.ApplicationID
,ap.AppInstanceID