To read the article online, visit http://www.4GuysFromRolla.com/webtech/041600-2.2.shtml

A Scalable Alternative to Session Variables, Part 2

By Scott Sargent


In Part 1 we discussed the benefits of using a GUID-based approach to managing the Session, and looked at the first of four stored procedures. In this part, we will look at the remaining three stored procedures, starting with the Second one, which is the most complex, responsible for writing the name value pairs to the database.


CREATE Procedure sessiondata
	@SESSIONID varchar (50),
	@SESSIONVALNAME Varchar(100),
	@SESSIONDATA Varchar(50)
AS
	Declare @CNTVALS int
	Select @CNTVALS=Count(*) FROM SESSIONVALUES 
              WHERE SESSIONID = @SESSIONID AND SESSIONVALNAME = SESSIONVALNAME
	
	IF (@CNTVALS > 0) 
	BEGIN
	--The Valname already Exisits, so simply update it
		UPDATE SESSIONVALUES
		SET SESSIONVALDATA = @SESSIONDATA
		WHERE SESSIONID = @SESSIONID AND SESSIONVALNAME = @SESSIONVALNAME
	
		RETURN 1 

	END
	ELSE
	BEGIN --there was no valname that corresponded to that session
		INSERT INTO SESSIONVALUES
		(SESSIONID, SESSIONVALNAME, SESSIONVALDATA)
		VALUES
		(@SESSIONID, @SESSIONVALNAME, @SESSIONDATA)
		
		RETURN 0 
	END

The third stored procedure. This one retrieves the name/value pairs

CREATE PROCEDURE retrievesession
	@SESSIONID varchar(50),
	@SESSIONVALNAME VARCHAR(100)
 AS
	SELECT SESSIONVALDATA FROM SESSIONVALUES 
              WHERE SESSIONID = @SESSIONID AND SESSIONVALNAME = @SESSIONVALNAME

The Fourth stored procedure. This is the one that will retrieve a lost GUID for a user. If the user clicks off the site & doesn't use the back button to come back (i.e. types the address or uses a bookmark) this method will retrieve the GUID he was originally assigned. If this cannot find it or finds 2 GUIDs it simply assigns him a new one:

CREATE PROCEDURE retrieve_GUID 
	@USRIPADDR VARCHAR(50)
AS
	DECLARE @CNTVALS INTEGER
	
	
	SELECT @CNTVALS=COUNT(*) FROM SESSIONS
	WHERE SESSIONIPADDR = @USRIPADDR AND 
	DATEDIFF(MINUTE, TIME_STAMP, GETDATE()) <= 20 --within 20 Minutes

	
IF (@CNTVALS = 1) --Only one value was returned, look up that GUID & return to user
		BEGIN
			SELECT SESSIONID FROM SESSIONS 
			WHERE SESSIONIPADDR = @USRIPADDR AND  
			DATEDIFF(MINUTE, TIME_STAMP, GETDATE()) <= 20 --within 20 Minutes
		END
ELSE
		BEGIN
		DECLARE @SESSIONID UNIQUEIDENTIFIER
		SET @SESSIONID = NEWID()
		
		INSERT INTO SESSIONS
		(SESSIONID, TIME_STAMP, SESSIONIPADDR)
		VALUES
		(@SESSIONID, getdate(), @USRIPADDR)
	
		SELECT SESSIONID FROM SESSIONS WHERE SESSIONID = @SESSIONID

		END

Now that we've examined the needed stored procedures, it's time to look at some ASP code. In Part 3 we'll look at how to get these four stored procedures to work together!

  • Read Part 3
  • Read Part 1

  • Article Information
    Article Title: A Scalable Alternative to Session Variables, Part 2
    Article Author: Scott Sargent
    Published Date: Sunday, April 16, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/041600-2.2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers