CREATE FUNCTION [dbo].[dump_var](@variable_name NVARCHAR(100)=NULL, @variable_value SQL_VARIANT=NULL, @delimiter NCHAR(1)='|') 

RETURNS NVARCHAR(4000)
AS
BEGIN

	
	RETURN N'{'+@variable_name + 
		   @delimiter + 
			CASE 
				WHEN @variable_value IS NULL THEN N'NULL'
				WHEN SQL_VARIANT_PROPERTY (@variable_value , 'BaseType')  IN ('DATE') THEN CONVERT(NVARCHAR(10),@variable_value,20)  
				WHEN SQL_VARIANT_PROPERTY (@variable_value , 'BaseType')  IN ('DATETIME','DATETIME2') THEN CONVERT(NVARCHAR(20),@variable_value,20)  
				ELSE CAST(@variable_value AS NVARCHAR(4000))
				END+N'}' 
	 

END

USE [SMMS]
GO
/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 11/01/2012 11:10:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[uspLogError] 
	@Variables [nvarchar] (4000) = NULL,
    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage],
            [Variables]
            ) 
        VALUES 
            (
            SYSTEM_USER, 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE(),
            @Variables
            );

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END; 

		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			
			SET @variables = dbo.dump_var('@var1',@var1,'|')
								+ dbo.dump_var('@var2',@var2,'|');

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
		END CATCH

declare @var1 int,
        @var2 varchar(10)


declare @xml xml

select @var1 = 3, @var2 = '41"'

Select @xml = (
select @var1 as var1, @var2 as var2 
for xml path)

select @xml

		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			

                        DECLARE @varaibales TABLE (@varaible NVARCHAR(100), @value slq_variant) 
			INSERT @varaibles VALUES('@var1', @var1);
                        INSERT @varaibles VALUES('@var2', @var2);

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
               END CATCH 

create type dbo.VariableValueList as table
(
 variable nvarchar(100) not null primary key,
 value sql_variant
);

		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			

                        DECLARE @varaibales dbo.VariableValueList;
			INSERT @varaibles VALUES('@var1', @var1);
                        INSERT @varaibles VALUES('@var2', @var2);

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
               END CATCH 

create type dbo.VariableValueList as table
(
 variable nvarchar(100) not null primary key,
 value sql_variant
);

		BEGIN CATCH 
		
			IF (XACT_STATE()) <> 0 ROLLBACK TRANSACTION;
			

                        DECLARE @varaibales dbo.VariableValueList;
			INSERT @varaibles VALUES('@var1', @var1);
                        INSERT @varaibles VALUES('@var2', @var2);

			EXEC dbo.uspLogError @variables = @variables;
			EXEC dbo.usp_RethrowError;
		
               END CATCH 

USE [SMMS]
GO
/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 11/01/2012 12:41:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[uspLogError] 
	@variables tvp_variables READONLY,
    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted
                                  -- by uspLogError in the ErrorLog table.

AS
BEGIN

	DECLARE @record_id INT; 


    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged.
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log.
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' 
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
        END;

        INSERT [dbo].[ErrorLog] 
            (
            [UserName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]) 
        VALUES 
            (
            SYSTEM_USER, 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );

		SET @ErrorLogId = @@IDENTITY;

		INSERT INTO tb_variables_errors(ErrorLogId, variable, value) 
		SELECT @ErrorLogId, variable, value FROM @variables;

        -- Pass back the ErrorLogID of the row inserted
        SELECT @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;