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
RadhaKrishnaKiJayaAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
< Knee-jerk reaction>  Add a SELECT to both blocks with the same schema, like lines 28 and 46 below..
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')                  
                  )
            SELECT 'INSERTED' as action   --  Looky here
            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
                  
                  SELECT 'UPDATED' as action   --  Looky here
             end  
END

Open in new window

If you really want to be spiff, if the table has an identify field you can do a SELECT SCOPE_IDENTITY() to return the newly-generated value.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
RadhaKrishnaKiJayaAuthor Commented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

1
 
RadhaKrishnaKiJayaAuthor Commented:
Thank you so much. It worked!!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  - Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.