Solved

Response back from stored procedure

Posted on 2016-10-05
6
102 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
[X]
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
  • 2
  • 2
  • 2
6 Comments
 
LVL 66

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 51

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 51

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 66

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

627 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