Jaya Prakash

Jaya Prakash

  • 523
  • 2.2k
  • 51.5k

How to Update in SqlServer

Mar 4 2024 4:48 PM

This is my type Table 

CREATE TYPE [dbo].[ThreeWayreconstatus] AS TABLE(
	[RowNo] [int] NOT NULL,
	[Status] [nvarchar](10) NOT NULL,
	[ReferenceId] [nvarchar](30) NOT NULL,
	[RequestID] [nvarchar](100) NOT NULL,
	[BankTransactionID] [nvarchar](30) NOT NULL,
	[TransactionDate] [nvarchar](255) NULL,
	[APIStatusCode] [nvarchar](10) NULL,
	[ServiceType] [nvarchar](155) NULL
)


here im trying to update my transactions column 3wayreconstatus to success but im unable to do it

ALTER PROCEDURE [dbo].[usp_UpdateThreeWayReconStatus]
-- Add the parameters for the stored procedure here
(
@ThreeWayreconstatus as ThreeWayreconstatus ReadOnly	
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
BEGIN TRY      
BEGIN TRANSACTION
SET NOCOUNT ON;

declare	   @RequestID NVARCHAR(100)         
          ,@STATUS NVARCHAR(10)         
		  ,@ReferenceId NVARCHAR(100)
		  ,@RequestAmount decimal(18,2)
		  ,@APIStatusCode nvarchar(10)

		  set @RequestID = (select RequestID from @ThreeWayreconstatus where RowNo =1)
		  SET @STATUS = (SELECT Status FROM @ThreeWayreconstatus) 
	

		  DECLARE @LoopCount int
          select @LoopCount =count(*) from @ThreeWayreconstatus
          DECLARE @n int = 1

		  IF (@LoopCount > 0)
		  BEGIN
		  while(@n<=@LoopCount)
		  BEGIN	
		   select 
           @RequestID= RequestID 
          ,@STATUS =Status
          ,@ReferenceId = ReferenceId
          ,@RequestID = RequestID 
          ,@APIStatusCode = APIStatusCode
          from @ThreeWayreconstatus TS where RowNo=@n
		   IF (SELECT count(*) FROM Transfers 
                    WHERE TransactionID = convert(NVARCHAR(100), @RequestID)) = 2
						  BEGIN
						 if(@STATUS='00')
						 BEGIN
						 Update Transactions set 3WayReconStatus='SUCCESS' where RequestID=@RequestID
						 End
						 else
						 BEGIN
						 select @RequestAmount = RequestAmount from Transactions where RequestID=@RequestID
PRINT @STATUS;
						-- Refund Logic Need  to write
						-- exec usp_InsertRazorPayWebhooks  @BankTransactionID, @Status, @PayoutID,@RequestID,@ResponseObject
         
						 END
						  SET @n = @n + 1
						  END

		  END	
		  SELECT 'You have been Updated ThreeWarycone Status Successfully' AS AlertMessage
		  END
		  ELSE
             BEGIN
                    SELECT 'Your ThreeWarycone Status Update Failed'  AS AlertMessage
             end
  
  

   END TRY 
   BEGIN CATCH       
  IF @@TRANCOUNT>0        

  BEGIN        

   ROLLBACK        

   INSERT INTO Application_Error(ErrorMessage,FromPage,Createdon) VALUES(ERROR_MESSAGE(),'usp_UpdateThreeWayReconStatus',GETDATE())        

  END        

END CATCH  
  
END

from code behind im sending  datatable first i've converted json object into datatable object

this how im doing 


my json

                        response = "{\"apiStatus\":true,\"apiStatusMessage\":\"RequestCompleted\",\"data\":[{\"merchantTransactionId\":\"BB3AEUAT3\",\"TransactionId\":\"fing1\",\"transactionRrn\":\"123rrn\",\"responseCode\":\"00\",\"referenceId\":\"35050520181634\",\"transactionDate\":\"28-04-2020\",\"serviceType\":\"CW\"},{\"merchantTransactionId\":\"2\",\"TransactionId\":\"fing2\",\"transactionRrn\":\"133rrn\",\"responseCode\":\"00\",\"referenceId\":\"35050520181634\",\"transactionDate\":\"28-04-2020\",\"serviceType\":\"AP\"}],\"apiStatusCode\":0}";

this is how im doing  

 DataTable dataTable = Tabulate(response);
 public static DataTable Tabulate(string json)
        {
            JObject jsonObj = JObject.Parse(json);

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("RowNo", typeof(int)));
            dt.Columns.Add(new DataColumn("Status", typeof(string)));
            dt.Columns.Add(new DataColumn("ReferenceId", typeof(string)));
            dt.Columns.Add(new DataColumn("RequestID", typeof(string)));
            dt.Columns.Add(new DataColumn("BankTransactionID", typeof(string)));
            dt.Columns.Add(new DataColumn("TransactionDate", typeof(string)));
            dt.Columns.Add(new DataColumn("APIStatusCode", typeof(int)));
            dt.Columns.Add(new DataColumn("ServiceType", typeof(string)));
            JArray dataArray = (JArray)jsonObj["data"];
            int iRowCounter = 1;
            foreach (JObject item in dataArray)
            {
                DataRow dr = dt.NewRow();
                dr["RowNo"] = iRowCounter++;
                dr["Status"] = (string)item["responseCode"];
                dr["ReferenceId"] = (string)item["referenceId"];
                dr["RequestID"] = (string)item["merchantTransactionId"];
                dr["BankTransactionID"] = (string)item["transactionRrn"];
                dr["TransactionDate"] = (string)item["transactionDate"];
                dr["APIStatusCode"] = (string)jsonObj["apiStatusCode"];
                dr["ServiceType"] = (string)item["serviceType"];
                dt.Rows.Add(dr);
            }

            return dt;
        }

after im getting like this

 

then im using my above procedure to update my table 
im getting error like

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
im literally helpless pls help me asap...


Answers (4)