CODEWITHSUNDEEP
c#sql-serverstored-procedures
user2525244
user2525244

Reputation: 101

How to use a function in a stored procedure sql server 2008

I have a table 

UserTime
(
    UserTimeId(pk)
    ,DAY
    ,Date
    ,CheckIn
    ,CheckOut
    ,HOURS
)

On run time when I click CheckIn button, it inserts current time

ALTER procedure [dbo].[InsertCheckIn]
   @UserID INT,
   @Day varchar(50),
   @Date nvarchar(50),
   @CheckIn nvarchar (50)
as
begin
    insert into UserTime (Userid, [Day], Date, CheckIn)
    values (@UserID, @Day, @Date, @CheckIn)
end

and on checkout, I insert the current time. 

ALTER procedure [dbo].[UpdateCheckOut]
   @UserID INT,
   @Day varchar(50),
   @Date nvarchar(50),
   @CheckOut nvarchar (50)
as
begin
    update UserTime
    set UserId   = @UserID
       ,[Day]    = @Day
       ,[Date]   = @Date
       ,CheckOut = @CheckOut
    from UserTime
    where UserId = @UserId
end

I have a function 

 DATEADD(SECOND, - DATEDIFF(SECOND, @CheckOut, @CheckIn), @HoursWorked)

that take a difference of the time values stored.

I want to store this difference in column Hours.

When I insert CheckIn -> store current time. On CheckOut, I want to store that time as well as the difference in Hours.

How can I implement this?

Upvotes: 0

Views: 205

Answers (1)

M.Ali
M.Ali

Reputation: 69594

Some suggestion and tips , Your table design needs some serious attention, I suggest you should have only three columns holding all the information that you are trying to store in multiple different fields with wrong datatypes.

CREATE TABLE UserTime
(
UserTimeId INT NOT NULL PRIMARY KEY
,CheckIn   DATETIME DEFAULT GETDATE()
,CheckOut  DATETIME
)
GO

Now have the following stored procedure to insert an entry the checkin time will get the current datetime by default.

ALTER procedure [dbo].[InsertCheckIn]
@UserTimeId INT
AS
BEGIN
  SET NOCOUNT ON;

    insert into UserTime (UserTimeId)
    values (@UserTimeId)

END

Now when the person leave the following stored procedure can check out only taking UserID and DATETIME for checkout.

ALTER procedure [dbo].[UpdateCheckOut]
@UserTimeId INT,
@CheckOut   DATETIME = NULL    --<-- If no value is provided it will 
AS                                 -- set it to current datetime here is set
BEGIN                              -- to default NULL
  SET NOCOUNT ON;
    IF (@CheckOut IS NULL)
      BEGIN
         SET @CheckOut = GETDATE();    -
      END 

    UPDATE UserTime
    SET CheckOut     = @CheckOut
    WHERE UserTimeId = @UserTimeId
END

Note

The other computed value you are trying to store in a column, you should avoid and write a query which gets that value at runtime when required.

Upvotes: 1

Related Questions