Wrapper

/********************************************************************************
*Enter Variables and execute to create and Print Stored Procedure Wrapper
*Try and figure out a way to script multiple SPs and have the wrapper for each one.
*1.Enter Database or double check Database
*2.Enter or double check Schema
*3.Enter Object Name or double Check Object Name
*4.Grant access to Public - this will allow all to execute the Object
********************************************************************************/DECLARE
@database NVARCHAR(MAX) = N''-- Enter Database
,@schema   NVARCHAR(MAX) = N''-- Enter Schema
,@SP_nameNVARCHAR(MAX) = N''-- Enter SP Name
,@SQL_codeNVARCHAR(MAX) = N''-- Enter SQL Code Here
,@objectNVARCHAR(MAX) = 'PROCEDURE'-- Enter Object type here
,@grantToPublic CHAR(3) = 'NO'-- Grant Access to Public? YES/NO
,@scriptdate SMALLDATETIME = GETDATE()
,@wrapperNVARCHAR(MAX) = N''
,@objectname NVARCHAR(MAX);

SET @objectname = (
SELECT
CASE WHEN @object = 'PROCEDURE'
THEN 'StoredProcedure '
WHEN @object = 'FUNCTION'
THEN 'UserDefinedFunction '
ELSE 'Not Defined'
END
)

SET @SQL_code = '';
/********************************************************************************
*EORROR CHECK LAST LINE OF DEFENCE
********************************************************************************/IF @database = '' OR @schema = '' OR @SP_name = ''
BEGIN
RAISERROR('ERROR - Check variable values! @database, @schema or @SP_name are BLANK!!!', 11, 1) WITH NOWAIT
RETURN
END;

/**************************************************************************************************/SET @database = @database + '.';
SET @schema = @schema + '.';
SET @wrapper = 'USE ' + REPLACE(@database,'.','') + ';
GO

/******Object:' + @objectname + @database + @schema + @SP_name + 'Script Date:' + CONVERT(CHAR(10), @scriptdate, 101) + ' ' + CONVERT(CHAR(8),CONVERT(TIME,@scriptdate)) + '******/
IF OBJECT_ID(' + CHAR(39) + @database + @schema + @SP_name + CHAR(39) + ') IS NOT NULL
BEGIN
DROP ' + @object + ' ' + @schema + @SP_name + '
IF OBJECT_ID('  + CHAR(39) +  @database + @schema + @SP_name  + CHAR(39) +  ') IS NOT NULL
PRINT ''<<< FAILED DROPPING ' + @object + ' ' + @database + @schema + @SP_name + ' >>>''
ELSE
PRINT ''<<< DROPPED ' + @object + ' ' + @database + @schema + @SP_name + ' >>>''
END;
GO

SET QUOTED_IDENTIFIER ON;
GO

SET ANSI_NULLS ON;
GO

/**************************************************************************************************/ -- SQL BEGIN

' + @SQL_code + '

/**************************************************************************************************
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************
***************************************************************************************************
**************************************************************************************************/
GO

SET NOCOUNT OFF;
GO

SET QUOTED_IDENTIFIER OFF;
GO

SET ANSI_NULLS ON;
GO
'
IF UPPER(@grantToPublic) = 'YES'
SET @wrapper = @wrapper + '
GRANT EXECUTE ON ' + @database + @schema + @SP_name + ' to Public ' + CHAR(10) + 'GO'
SET @wrapper = @wrapper + '
IF OBJECT_ID(' + CHAR(39) + @database + @schema + @SP_name + CHAR(39) + ') IS NOT NULL
PRINT ''<<< CREATED ' + @object + ' ' + @database + @schema + @SP_name + ' >>>''
ELSE
PRINT ''<<< FAILED TO CREATE ' + @object + ' ' + @database + @schema + @SP_name + ' >>>''
GO
'

/**************************************************************************************************/
PRINT @wrapper;