Resumé

Row_Number, Try Catch and Raiserror in SQL 2005

Cristian Merighi () 4.00

Let's see how to use some new SQL 2005 features to develop a procedure which traces the accesses to our website deleting, at the same time, exceeding old rows.
This article is obsolete. Some functionalities might not work anymore. Comments are disabled.

New SQL Server 2005 brings a lot of interesting features for all those who develop data-driven application (that means every webdeveloper and almost everyone among the others).
The features I'm about to write in this article are:

  • ROW_NUMBER function (and relative OVER clause);
  • RAISERROR function;
  • and, stricly related, TRY...CATCH statement.

A suitable scenario to give location to our new members could be the following one:
let's suppose we have to trace the visits our website receives by inserting data in a table of our SQL 2005 database.
At a particular condition:
we don't want to overload of old and unuseful data our hard-disk; so, everytime we insert a new record we havve to care about deleting all the records over the nth row (ROW_NUMBER function serves for this goal).
The operation are done using a stored procedure which handles a transaction.
If an exception occurrs (TRY...CATCH) a RollBack must be executed to restore the previous status, then the original exception must be thrown (RAISERROR) to fave debugging activities.

The code of this stored procedure could be the following:


-- =============================================
-- Author:      Cristian Merighi
-- Create date: 2006/08/04
-- Description:	Visits tracing proc
-- =============================================
CREATE PROCEDURE [dbo].[sp_visitsInsert] (
	@SessionID varchar(30),
	@UrlReferer varchar(100) = '',
	@PageUrl varchar(150),
	@IPAddress varchar(15),
	@Browser varchar(50),
	@UserAgent varchar(200),
	@ClientCaps varchar(200) = NULL,
	@Languages varchar(200) = '',
	@IsSpider bit= 0,
	@NewID int= 0 OUTPUT) 	/* output parameter
                                   to retrieve the ID
                                   just inserted
				*/
AS
BEGIN
	
	SET NOCOUNT ON;

	-- beginning the transaction
    	BEGIN TRANSACTION 
    
	BEGIN TRY -- TRY Statement
		INSERT INTO [Visits]
			   ([strUrlReferer]
			   ,[strSessionID]
			   ,[strPageUrl]
			   ,[strIPAddress]
			   ,[strBrowser]
			   ,[strUserAgent]
			   ,[strClientCaps]
			   ,[strLanguages]
			   ,[bitSpider])
		 VALUES
			   (@UrlReferer
			   ,@SessionID
			   ,@PageUrl
			   ,@IPAddress
			   ,@Browser
			   ,@UserAgent
			   ,@ClientCaps
			   ,@Languages
			   ,@IsSpider)

		-- 1000 is the maximum number of 
		-- records we want to store in our table
		DELETE FROM Visits WHERE ID IN
		(
		SELECT ID FROM(
		SELECT ID, ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNumb
		FROM Visits) AS VisitsIndexed
		WHERE VisitsIndexed.RowNumb> 1000)

		-- output parameter
		SELECT @NewID = SCOPE_IDENTITY()
		
		COMMIT TRANSACTION
		
	END TRY
	BEGIN CATCH
		-- CATCH block to intercept exceptions
		ROLLBACK TRANSACTION
		DECLARE @ErrorMessage nvarchar(4000);
		DECLARE @ErrorSeverity int;
		DECLARE @ErrorState int;

		SELECT
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();

		-- Use RAISERROR inside the CATCH block to return error
		-- information about the original error that caused
		-- execution to jump to the CATCH block.
		RAISERROR (	@ErrorMessage, -- Message text.
				@ErrorSeverity, -- Severity.
				@ErrorState -- State.
				);

	END CATCH
	
END

TRY..CATCH
The statement is pretty intuitive to use remembering the similar ones in .NET, javascript...

RAISERROR
Function similar to .NET throw function (for an exhaustive documentation please consult the SQL 2005 reference).

ROW_NUMBER
This function must be accompanied by the OVER clause and returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
I'll post soon an example about how to use this particular function to improve paging data performances exploiting all the possibilities offered by the new .Net 2.0 DataSource Objects.

Take care. Bye.

Feedbacks

  • Re: Row_Number, Try Catch and Raiserror in SQL 2005

    ssisXpert Tuesday, April 07, 2009 4.00

    Adding the rownum is a small stroke of genius. I come across many situations where I have to restart the job at a particular point, rather than rolling back the entire transaction. This is great. Thanks

feedback
 

Syndicate

Author

Cristian Merighi facebook twitter google+ youtube

Latest articles

Top rated

Archive

Where am I?

Author

Cristian Merighi facebook twitter google+ youtube

I'm now reading

Feeds