--use CAS_DEV ;
/****** Object: StoredProcedure [dbo].[procUtils_ExampleProc] Script Date: 03/23/2010 14:31:38 ******/
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_ExampleProc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procUtils_ExampleProc]
GO
/****** Object: StoredProcedure [dbo].[procUtils_ExampleProc]
See doc bellow
Script Date: 03/23/2010 14:31:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procUtils_ExampleProc]
-- BEGIN DEFAULT SQL API
@UserSessionId [bigint],
@PageTypeId [int],
@Msg [varchar](200) OUTPUT,
@DebugMsg [varchar](200) OUTPUT,
@Ret [int] OUTPUT
-- END DEFAULT SQL API
AS
BEGIN -- proc start
SET NOCOUNT ON;
declare @ThisProcName varchar(200)
declare @ProcStep varchar(4000)
set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))
set @Msg = ''
set @DebugMsg = ''
set @ProcStep = ''
DECLARE @WinNewLine nvarchar(2)
set @WinNewLine = CONVERT ( nvarchar(2) , CHAR(13) + CHAR(10))
DECLARE @Tab nvarchar(1)
set @Tab = CONVERT(NVARCHAR(1) , CHAR(9))
BEGIN TRY --begin try
set @Ret = 1 --assume false from the beginning
set @Msg = 'The Msg To Show to the user'
set @Ret = @@ERROR
-- action !!! put code hrere
return @Ret
END TRY --end try
BEGIN CATCH
PRINT 'In CATCH block.
Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
Error message: ' + ERROR_MESSAGE() + '
Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));
set @Msg = 'Failed to retrieve Report '
set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +
'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))
--record the error in the database
set @DebugMsg = @DebugMsg + @Msg
set @Ret = 1
END CATCH
return @Ret
END --PROC END
/*
<ProcName> procUtils_ExampleProc </ProcName>
<doc>
Generates the Update Procedure on any table a
</doc> <use>
EXEC [dbo].[procUtils_GenerateUpdateProc] @TableName = N'Whatever'
</use> */
GO
-- START PERMISSIONS
GRANT EXECUTE ON [dbo].[procUtils_ExampleProc] TO [ReplaceHereDbRole] AS [dbo]
GO
-- END PERMISSIONS
Posts
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!