Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Response back from stored procedure

Posted on 2016-10-05
6
75 Views
Last Modified: 2016-10-06
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
Comment
Question by:RadhaKrishnaKiJaya
  • 2
  • 2
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41830572
< 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
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41831279
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 41832005
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41832028
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
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 41832145
Thank you so much. It worked!!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41832177
Thanks for the grade.  Good luck with your project.  - Jim
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

829 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