ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Stored Procedure to return value based on Transaction in SQL Server<p>Hi <a class="username" rel="amar"> amar</a>,</p> <p>Check this test query. Now please take its reference and correct your query.</p> <p><strong><span style="text-decoration: underline;">SQL</span></strong></p> <pre class="brush: sql">CREATE PROCEDURE [dbo].[InsertUpdateCustomer] ( @ID int = NULL, @Name varchar(max)= NULL, @Country varchar(max)= NULL ) AS BEGIN DECLARE @Out INT BEGIN TRANSACTION IF EXISTS(SELECT * FROM Customers(NOLOCK) WHERE CustomerId = @ID AND Country = @Country) BEGIN UPDATE Customers SET Name = @Name WHERE CustomerId = @ID END ELSE BEGIN INSERT INTO Customers (Name,Country) VALUES (@Name,@Country) END IF @@ERROR &lt;&gt; 0 BEGIN ROLLBACK TRANSACTION SET @Out = 0 END ELSE BEGIN COMMIT TRANSACTION SET @Out = 1 END SELECT @Out &#39;Status&#39; END GO -- Executed Successfully so Transaction Comitted. EXEC InsertUpdateCustomer 1,&#39;John Hammond&#39;,&#39;United States&#39; GO -- Error so Transaction Rolledback. EXEC InsertUpdateCustomer 2,&#39;John Hammond&#39;,&#39;United States&#39; </pre> <p><strong><span style="text-decoration: underline;">Screenshot</span></strong></p> <p><img src="" alt="" width="459" height="284" /></p>, 09 Oct 2018 05:41:32 GMT