[SOLVED] TSQL NOLOCK VIEW and stored procedure

TSQL NOLOCK VIEW and stored procedure

In our company we tend to use views and stored procedures.

We've recently started to implement the NOLOCK statement to a lot of our views.

I was wondering: if I am applying NOLOCK to a view, it "trickles down" to the stored procedure

Say I have a view called viewPartyPackage and view statement was...

SELECT   
    PartyPackageID, Name, Created, LastModified, Deleted 
FROM        
    dbo.PartyPackage WITH (NOLOCK) 
WHERE     
    (Deleted = 0)

and also I had a stored procedure:

ALTER proc [dbo].[partypackage_Select]
    (@PartyPackageID bigint = null) 
AS 
    SELECT * 
    FROM [viewPartyPackage] PartyPackage 
    WHERE (@PartyPackageID IS NULL OR @PartyPackageID = [PartyPackageID])

Would I lose the NOLOCK feature because I'm calling from a stored procedure and in turn would I need to put a (NOLOCK) on the stored procedure as well? Or does the NOLOCK that's in the view come into play?

Solution

See the answers to this SO question. To quote:

See Table Hints in MSDN: "In SQL Server 2005, all lock hints are propagated to all the tables and views that are referenced in a view. Also, SQL Server performs the corresponding lock consistency checks."