stored procedure problem

Question

if we are using the transaction no lock in stored procedure then it will also applicable in functions used inside the procedure or not ?

Nitin Sontakke · Answer

The simplest answer is, you cannot. Furthermore, the SQL Server community largely discourage this practice altogether.

Brahma Prakash Shukla · Answer

So, How we use no lock in whole function used inside the stored procedure and stored procedure contains no lock ? Note:- we don't need to use no lock in queries used in the function.

Nitin Sontakke · Answer

It seems you mean to say HINT (no lock). If that is what you mean, then the answer is no.

Anandu G Nath · Answer

In SQL Server, the behavior of transactions with respect to locking extends to functions called within a stored procedure, depending on how these functions are implemented and their transaction handling. When a stored procedure is executed within a transaction context with the NOLOCK hint (or other isolation levels like READ UNCOMMITTED ), the behavior inside the stored procedure, including the functions it calls, will also adopt the specified isolation level. CREATE PROCEDURE UpdateEmployeeSalary @employeeID INT, @newSalary DECIMAL(10, 2) AS BEGIN BEGIN TRANSACTION; -- Update employee salary in the Employee table UPDATE Employee SET Salary = @newSalary WHERE ID = @employeeID; -- Call the function to get employee details SELECT * FROM dbo.GetEmployeeDetails() WITH (NOLOCK); -- Commit or rollback the transaction based on business logic -- COMMIT TRANSACTION; -- ROLLBACK TRANSACTION; END;