Declaring and setting variable in upsert SQL Server query no

I have a problem with declaring and setting variable in my upsert sql query.

public void UpdateTrippingTariff(List<TrippingTariffTransaction> obj)

using (IDbConnection connection = ConnectionManager.Connection)

string qry = "";

foreach (var details in obj.ToList())

qry = "DECLARE @TripTariffTransactionID as BIGINT;";
qry += "SET @TripTariffTransactionID = (SELECT Id FROM [dbo].[TrippingTariffTransaction] WHERE Trip = @Trip AND TrippingDistanceId = @TrippingDistanceId AND TrippingTariffId = @TrippingTariffId);";
qry += @" IF (@TripTariffTransactionID != 0)
UPDATE [dbo].[TrippingTariffTransaction]
SET Price = @Price
WHERE Id = @TripTariffTransactionID
ELSE
INSERT INTO [dbo].[TrippingTariffTransaction]
(TrippingTariffId, Trip, Price, TrippingDistanceId, IsActive)
VALUES (@TrippingTariffId, @Trip, @Price, @TrippingDistanceId, @IsActive);
SET @TripTariffTransactionID = (SELECT CAST(SCOPE_IDENTITY() as BIGINT));";

qry += @"INSERT INTO [dbo].[TrippingTariffTransactionAuditTrail]
(LogDatetime, MasterlistId, ComputerName, TrippingTariffTransactionID, Activity)
VALUES (GETDATE(), @MasterlistId, @ComputerName, @TripTariffTransactionID, @Activity)";

if (connection.State == ConnectionState.Closed)

connection.Open();


connection.Execute(qry, details);
connection.Close();





I'm getting a null values in the TripTariffTransactionID column in the TrippingTariffTransactionAuditTrail table.

TripTariffTransactionID

TrippingTariffTransactionAuditTrail

What should I change on my query? Thanks in advance

@TripTariffTransactionID != 0

@TripTariffTransactionID IS NOT NULL

1 Answer
1

I solved my problem using this approach

public void UpdateTrippingTariff(List<TrippingTariffTransaction> obj)

using (IDbConnection connection = ConnectionManager.Connection)

string qry = "";
foreach (var details in obj.ToList())

qry = "DECLARE @TripTariffTransactionID as BIGINT;";
qry += "SET @TripTariffTransactionID = (SELECT Id FROM [dbo].[TrippingTariffTransaction] WHERE Trip = @Trip AND TrippingDistanceId = @TrippingDistanceId AND TrippingTariffId = @TrippingTariffId);";
qry += @" IF (@TripTariffTransactionID != 0)
BEGIN
UPDATE [dbo].[TrippingTariffTransaction]
SET
Price = @Price
WHERE Id = @TripTariffTransactionID
END

ELSE
BEGIN
INSERT INTO [dbo].[TrippingTariffTransaction]
(TrippingTariffId
,Trip
,Price
,TrippingDistanceId
,IsActive)
VALUES
(@TrippingTariffId
,@Trip
,@Price
,@TrippingDistanceId
,@IsActive);
SET @TripTariffTransactionID = (SELECT CAST(SCOPE_IDENTITY() as BIGINT))
END;";

qry += @"INSERT INTO [dbo].[TrippingTariffTransactionAuditTrail]
(LogDatetime
,MasterlistId
,ComputerName
,TrippingTariffTransactionID
,Activity)
VALUES
(GETDATE()
,@MasterlistId
,@ComputerName
,@TripTariffTransactionID
,@Activity)";

if (connection.State == ConnectionState.Closed)

connection.Open();

connection.Execute(qry, details);
connection.Close();





UPDATE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.