stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-06 : 07:47:12

Eugene writes "I have created the following table in ms sql:

create table tbl_users
(
SessionID varchar(255) Primary Key,
usID Varchar(20),
Password Varchar(20),
LastUpdate Smalldatetime
);

Bellow is the stored procedure:

Create Procedure usp_CheckSessionID
@sessionID Varchar(255)
As SET NOCOUNT ON

if EXISTS(SELECT top 1 * FROM tbl_users WHERE sessionID=@sessionID AND DATEDIFF(n,LastUpdate,GETDATE())<=20)
begin
update tbl_users set LastUpdate = GETDATE() WHERE sessionID=@sessionID
Select 0
end
else
Select -1

Return
GO

When ever I execute the store procedure as bellow:

execute usp_CheckSessionID "{A83A94F2-C7AC-46A7-A7E7-7F3447A4B2F3}"

It returns a -1 despite the record with the sessionID A83A94F2-C7AC-46A7-A7E7-7F3447A4B2F3 exist in the database and the DATEDIFF(n,LastUpdate,GETDATE())<=20). Even when I enter an invalid session ID it also returns a -1.

Your help is kindly appreciated."

vipinspg
Starting Member

12 Posts

Posted - 2005-07-06 : 08:23:11

If you want to check whether a particular session exists, you should not use "top 1". You need to change like if EXISTS (SELECT * from tbl_users WHERE sessionID=@sessionID AND DATEDIFF(n,LastUpdate,GETDATE())<=20) ......

.

SamC
White Water Yakist

3467 Posts

Posted - 2005-07-06 : 08:28:54

Your query could be written differently... however, to find out what's wrong with your query, make the following change and inspect the return values...


 if EXISTS(SELECT top 1 * FROM tbl_users WHERE sessionID=@sessionID AND DATEDIFF(n,LastUpdate,GETDATE())<=20)
begin
  update tbl_users set LastUpdate = GETDATE() WHERE sessionID=@sessionID
  Select 0
end
else
    Select sessionID, LastUpdate, DATEDIFF(n,LastUpdate,GETDATE()) from tbl_users WHERE sessionID=@SessionID

An empty result set will tell you you're not matching sessionID, anything else will show you the LastUpdate and DATEDIFF values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources