When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Sunday, April 16, 2000

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.

- continued -

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

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article