ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. 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="https://imgur.com/0hsAggR.jpg" alt="" width="459" height="284" /></p>https://www.aspforums.net:443/Threads/104937/Stored-Procedure-to-return-value-based-on-Transaction-in-SQL-Server/https://www.aspforums.net:443/Threads/104937/Stored-Procedure-to-return-value-based-on-Transaction-in-SQL-Server/Tue, 09 Oct 2018 05:41:32 GMT