asked on

MSSQL SP dateadd problem...

I bet this is an easy one.. But I`m totally stuck...

1:I have a table dbo.DayReg that is supposed to contain one row of info for each day the system is in use. The first thing that happends when a user logs on is that this SP is run and the returnvalue is captured.

2:If there is no record for today, one should be inserted. If there is a record for today, the DID should be returned.

3: a day is defined as today 00:00:00 to 23:59:59

Whats wrong with this one? It just behaves strange without any logic...
W2K3+MSSql2K

__________________________________________________
Create procedure [Checkday] AS

Declare @retvalue int

If exists(select * from dbo.DayReg where DS>dateadd(day, -1,getdate()))
set @retvalue=(select top 1 DID from dbo.DayReg)

If NOT exists (select * from dbo.DayReg where DS>dateadd(day, -1,getdate()))
insert into dbo.DayReg (Week) values (datepart(week,getdate()))
select @@identity
set @retvalue=@@identity

return @retvalue
Go
_____________________

muzzy2003

You need to use the following code, as at the moment you are comparing the previous 24 hour period, not just the same day. Also, your three commands after the second IF all need to be grouped with BEGIN ... END.

Create procedure [Checkday] AS

Declare @retvalue int

If exists(select * from dbo.DayReg where DATEDIFF(day, DS, GETDATE()) = 0)
set @retvalue=(select top 1 DID from dbo.DayReg)

If NOT exists (select * from dbo.DayReg where DATEDIFF(day, DS, GETDATE()) = 0)
begin
      insert into dbo.DayReg (Week) values (datepart(week,getdate()))
      select @@identity
      set @retvalue=@@identity
end

return @retvalue
Go

Or, a little neater:

Create procedure [Checkday] AS

Declare @retvalue int

select @retvalue = * from dbo.DayReg where DATEDIFF(day, DS, GETDATE()) = 0)

IF @retvalue IS Null
begin
      insert into dbo.DayReg (Week) values (datepart(week,getdate()))
      select @@identity
      set @retvalue=@@identity
end

return @retvalue
Go

mattisflones

ASKER

Hi, thanx for helping.. I see where you are going with this.. and it makes sense!

But... While testing your second approach i get
Error 116: Only one expression can be specified in the select list when the sub query is not introduced with EXISTS"

And there seems to be something wrong here:

"select @retvalue = * from dbo.DayReg where DATEDIFF(day, DS, GETDATE()) = 0)"

Isn`t set the correct way to set a variable?!

ASKER CERTIFIED SOLUTION

muzzy2003

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mattisflones

ASKER

This one worked!

________________________________
Create procedure [Checkday] AS

Declare @retvalue int
If Exists (select * from dbo.DayReg where DATEDIFF(day, DS, GETDATE()) = 0)
Begin
set @retvalue =(select top 1 DID from dbo.DayReg order by DS Desc)
End

IF @retvalue IS Null
begin
insert into dbo.DayReg (Week) values (datepart(week,getdate()))
select @@identity
set @retvalue=@@identity
end

return @retvalue
Go
_______________________

mattisflones

ASKER

Awright.. Now yours works too...

Thanx for the help! Have a nice morning...

muzzy2003

No problem.