[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL SP dateadd problem...

Posted on 2004-11-22
6
Medium Priority
?
354 Views
Last Modified: 2012-06-27
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
_____________________
0
Comment
Question by:mattisflones
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12652142
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
0
 
LVL 15

Author Comment

by:mattisflones
ID: 12652184
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?!
0
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 1000 total points
ID: 12652198
Sorry - should be:

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

It's only 7 am here, and I'm groggy!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Author Comment

by:mattisflones
ID: 12652203
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
_______________________
0
 
LVL 15

Author Comment

by:mattisflones
ID: 12652222
Awright.. Now yours works too...

Thanx for the help! Have a nice morning...
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12652358
No problem.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question