USE MASTER
GO
IF OBJECT_ID('dbo.sp_ExecTemplate') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_ExecTemplate AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_ExecTemplate
@ObjectName SYSNAME = NULL,
@SQL VARCHAR(MAX) = '' OUTPUT,
@PrintDefault BIT = 0,
@DeclareReturn BIT = 0,
@PrintReturnLine BIT = 0,
@DoNotDeclareVar BIT = 1,
@PrintColumnType BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ObjectID INT = OBJECT_ID(LTRIM(RTRIM(@ObjectName)))
IF @ObjectID IS NULL
BEGIN
PRINT 'EXEC sp_ExecTemplate
@ObjectName = NULL,
@PrintDefault = 0,
@DeclareReturn = 1,
@PrintReturnLine = 1,
@DoNotDeclareVar = 0,
@PrintColumnType = 1
GO'
RETURN
END ;
WITH pr
AS (SELECT pm.parameter_id AS Id,
pr.HasDefault,
pr.VALUE AS DefaultValue,
ct.ColumnType,
pm.is_output AS IsOutput,
pm.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ParameterName
FROM sys.all_sql_modules m (NOLOCK)
INNER JOIN sys.procedures p ON p.OBJECT_ID = m.OBJECT_ID
INNER JOIN sys.parameters pm (NOLOCK) ON pm.OBJECT_ID = m.OBJECT_ID
CROSS APPLY util.dbo.ParseSPDefaults(m.uses_quoted_identifier, m.definition) pr
INNER JOIN sys.types y (NOLOCK) ON y.user_type_id = pm.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(y.name, pm.max_length, pm.PRECISION, pm.scale, db.collation_name, db.collation_name) ct
WHERE m.OBJECT_ID = @ObjectID
AND pr.ParameterId = pm.parameter_id),
Param01
AS (SELECT TOP 999999
ParameterName,
IsOutput,
ColumnType AS ColDef,
DefaultValue,
HasDefault,
Id
FROM pr
ORDER BY Id)
SELECT @SQL = CASE WHEN @DoNotDeclareVar = 1
THEN 'EXEC ' + @ObjectName + '
' + Util.dbo.StringConcat(' ' + CASE WHEN Id > 1 THEN ','
ELSE ''
END + ParameterName + ' = ' + ISNULL(DefaultValue, 'NULL')
+ CASE WHEN IsOutput = 1 THEN CASE WHEN @PrintColumnType = 1 THEN ' /*' + ColDef + ' OUTPUT*/'
ELSE ' /*OUTPUT*/'
END
ELSE CASE WHEN @PrintColumnType = 1 THEN ' /*' + ColDef + '*/'
ELSE ''
END
END, '
') ELSE 'DECLARE
' + CASE WHEN @DeclareReturn = 1 THEN ' @ReturnVal int
' ELSE ''
END + Util.dbo.StringConcat(' ' + CASE WHEN @DeclareReturn = 1 THEN ','
WHEN Id > 1 THEN ','
ELSE ''
END + ParameterName + ' ' + ColDef + ISNULL(' = ' + DefaultValue, ''), '
') + '
EXEC @ReturnVal = ' + @ObjectName + '
' + Util.dbo.StringConcat(CASE WHEN Id > 1 THEN ','
ELSE ''
END + ParameterName + ' = ' + ParameterName + CASE WHEN IsOutput = 1 THEN ' OUTPUT'
ELSE ''
END + CASE WHEN @PrintDefault = 1
AND HasDefault = 1
THEN ISNULL(' /*DEFAULT: ' + DefaultValue + '*/', '')
ELSE ''
END, '
') + CASE WHEN @PrintReturnLine = 1 THEN '
SELECT @ReturnVal as ''@ReturnVal'', ' + Util.dbo.StringConcat(ParameterName + ' AS ''' + ParameterName + '''', ', ')
ELSE ''
END
END
FROM Param01
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_ExecTemplate
GO