Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

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
0
RadhaKrishnaKiJaya
Asked:
RadhaKrishnaKiJaya
  • 2
  • 2
  • 2
1 Solution
 
Jim HornMicrosoft 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now