ÿþ/****** Object:  StoredProcedure [dbo].[TRUNCATE_TBL]    Script Date: 02/22/2010 14:23:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create Procedure [dbo].[TRUNCATE_TBL] @tbl varchar(1000),@owner varchar(30)='dbo'

      WITH EXECUTE AS SELF

as 

begin



	Declare @username varchar(30)

	Set @username = Original_login()





	If exists (select * from dba.truncate_list

					Where [List_Table_Name] = @tbl)

	Begin

		  Declare @rc int

		  Declare @truncate_Audit_id int

		  Insert into dba.truncate_Audit (table_name,table_owner,username)

									Values (@tbl,@owner,@userName)

		  Set @truncate_audit_id = scope_identity()

	 

		  declare @dynamic_sql nvarchar(1000)

		  set @dynamic_sql = 'Truncate Table ' + @owner + '.' + @tbl

	 

		  --Print @dynamic_sql

		  set @rc = -1

		  BEGIN TRY

				exec @rc = sp_executeSQL @dynamic_SQL

		  END TRY

		  BEGIN CATCH

					DECLARE @ErrorMessage    NVARCHAR(4000),

						@ErrorNumber     INT,

						@ErrorSeverity   INT,

						@ErrorState      INT,

						@ErrorLine       INT,

						@ErrorProcedure  NVARCHAR(200);

			SELECT @ErrorNumber = ERROR_NUMBER(),

				@ErrorSeverity = ERROR_SEVERITY(),

				@ErrorState = ERROR_STATE(),

				@ErrorLine = ERROR_LINE(),

				@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');



		SELECT @ErrorMessage = 

			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 

			'Message: '+ ERROR_MESSAGE();



		-- Raise an error: msg_str parameter of RAISERROR will contain

		-- the original error information.

		RAISERROR (@ErrorMessage,@ErrorSeverity,1,

			@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorProcedure,

			@ErrorLine)



			RETURN @RC

		  END CATCH



				Update dba.truncate_audit

					  Set success = @rc

					  Where truncate_audit_id = @truncate_audit_id

	End

	ELSE

	Begin

		--DECLARE @rc int

		SET NOCOUNT ON

		DECLARE @truncate_Audit_id1 int

		  INSERT dba.truncate_fail (table_name_input,table_owner,username)

		  VALUES (@tbl,@owner,@userName)

		  Set @truncate_audit_id1 = scope_identity()

		RAISERROR('Cannot find the object because it does not exist or you do not have permissions.',

			16,1,1088,16,7,'[dbo].[TRUNCATE_TBL]',1)

		Return -1

	End

END