asked on

Response back from stored procedure

Hi Experts,

This is my stored procedure. It either executes Insert or update. How can I send a response back to my program, telling whether it did the insert or update

Thanks in advance.

Alter PROCEDURE [dbo].[SPINSERTUPDATELOGINTIME]
@UserID int


AS

BEGIN

if not exists(
            select StdLogID,StdTimeIn from LOGINDETAILS where userID = @UserID
            and TodaysDate =FORMAT(GetDate(),'MMddyyyy')
            and (DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60 or (StdTimeIn=GetDate()))
            and StdTimeIn IS NOT NULL and StdTimeout IS NULL
            )
            --First check if any record exists with the userID.
            --If not then insert it
            begin
            INSERT INTO dbo.LOGINDETAILS
                  (
                        UserID,
                        StdTimeIn,
                  TodaysDate
                  )
            VALUES
                  (
                        @UserID,
                        CURRENT_TIMESTAMP,
                        FORMAT(GetDate(),'MMddyyyy')
                  )

            end

else
      --If the record exists then we need to update it. But check if it is created before 1 min
      if not exists(
            select StdLogID,StdTimeIn from LOGINDETAILS where userID = @UserID
            and TodaysDate =FORMAT(GetDate(),'MMddyyyy')
            and (DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60 or (StdTimeIn=GetDate()))
            and StdTimeIn IS NOT NULL and StdTimeout IS NULL
            )

             begin
                  UPDATE dbo.LOGINDETAILS SET
                  StdTimeout=CURRENT_TIMESTAMP
                  Where TodaysDate =FORMAT(GetDate(),'MMddyyyy') and
                  StdTimeIn IS NOT NULL and StdTimeout IS NULL
             end
END

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

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

Start Free Trial

Vitor Montalvão

Stored Procedure accepts output parameters. Output parameters are parameters where you can store values so it's used for returning values. For you case:

Alter PROCEDURE [dbo].[SPINSERTUPDATELOGINTIME]
        @UserID    int,
        @Operation char(1) OUTPUT -- 'I'-Insert, 'U'-Update
                           
 AS 

 BEGIN 

 if not exists(
             select StdLogID,StdTimeIn from LOGINDETAILS  where userID = @UserID 
             and TodaysDate =FORMAT(GetDate(),'MMddyyyy')
             and (DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60 or (StdTimeIn=GetDate()))
             and StdTimeIn IS NOT NULL and StdTimeout IS NULL
             )
            --First check if any record exists with the userID.
             --If not then insert it
             begin
                   INSERT INTO dbo.LOGINDETAILS 
                   (
                         UserID,               
                         StdTimeIn,
                       TodaysDate     
                   ) 
                  VALUES 
                   ( 
                         @UserID,                                     
                         CURRENT_TIMESTAMP,
                         FORMAT(GetDate(),'MMddyyyy')                  
                   )
                   
                  SET @Operation = 'I' -- Insert
             end

  else
       --If the record exists then we need to update it. But check if it is created before 1 min
       if not exists(
             select StdLogID,StdTimeIn from LOGINDETAILS  where userID = @UserID 
             and TodaysDate =FORMAT(GetDate(),'MMddyyyy')
             and (DATEDIFF(SECOND, StdTimeIn,GetDate()) < 60 or (StdTimeIn=GetDate()))
             and StdTimeIn IS NOT NULL and StdTimeout IS NULL
             )

              begin
                   UPDATE dbo.LOGINDETAILS 
                   SET   StdTimeout=CURRENT_TIMESTAMP
                   Where TodaysDate =FORMAT(GetDate(),'MMddyyyy') and
                   StdTimeIn IS NOT NULL and StdTimeout IS NULL

                   SET @Operation = 'U' -- Update
              end  
 END

Open in new window

RadhaKrishnaKiJaya

ASKER

Thanks for your reply. Any idea how can I get the value in my vb.net program? This is my code.

Public Function Insert_Update_Time(UserID As Integer) As String
Insert_Update_Time = "FALSE"
Dim con As New SqlConnection(sqlConnectionString)
Try
Dim cmd As New SqlCommand
Dim prmUserID As SqlParameter

cmd.CommandText = "SPINSERTUPDATELOGINTIME"
cmd.CommandType = CommandType.StoredProcedure

prmUserID = New SqlParameter("@UserID", UserID)
cmd.Parameters.Add(prmUserID)
cmd.Connection = con
con.Open()
Dim count As Integer = cmd.ExecuteNonQuery()
If count = 1 Then
Insert_Update_Time = "TRUE"
Else
Insert_Update_Time = "NONE"
End If
con.Close()
Catch ex As Exception
Error_Log(ex.Message, Now(), "Insert_Update_Time")
End Try
End Function

Vitor Montalvão

Try to add the new parameter and add the following to your code:

Public Function Insert_Update_Time(UserID As Integer) As String
...
        Dim prmUserID As SqlParameter
        Dim prmOperation As SqlParameter
        ...
        cmd.Parameters.Add(prmUserID)
        cmd.Parameters.Add(prmOperation).Direction = ParameterDirection.Output
        ...
       If prmOperation='I' Then
             Insert_Update_Time = "TRUE"
       Else
             Insert_Update_Time = "NONE"
       End If
       ...

Open in new window

RadhaKrishnaKiJaya

ASKER

Thank you so much. It worked!!

Jim Horn

Thanks for the grade. Good luck with your project. - Jim