Solved

Response back from stored procedure

Posted on 2016-10-05
6
64 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 47

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 47

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

786 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