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_name	NVARCHAR(MAX) = N''			-- Enter SP Name
,		@SQL_code	NVARCHAR(MAX) = N''			-- Enter SQL Code Here
,		@object	NVARCHAR(MAX) = 'PROCEDURE'	-- Enter Object type here
,		@grantToPublic CHAR(3) = 'NO'			-- Grant Access to Public? YES/NO
,		@scriptdate SMALLDATETIME = GETDATE()
,		@wrapper	NVARCHAR(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;