Can I achieve WITH(NOLOCK) on all joins in a stored procedure with a single command?

I have a number of reporting stored procedures that purely list
records and make no changes to the data. I have noticed that some of
these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
a simple example :-
Select * from table1 WITH(NOLOCK)
INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
There lots of these and many of them have lots of joins so I'm looking
for a way to apply WITH(NOLOCK) to the whole procedure and save myself
the time it takes to add the hint to each table/join. I know that it
is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
volunteer as the deadlock victim, but this isn't suitable as I need
the procedure to return it's records.
Does anybody have a suggestion or am I looking ata couple of days of
ctrl-v'ing WITH(NOLOCK) everywhere?
Thanks,
LiamHow about below?
SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Liam Weston" <liam_weston@.hotmail.com> wrote in message
news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> I have a number of reporting stored procedures that purely list
> records and make no changes to the data. I have noticed that some of
> these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> a simple example :-
> Select * from table1 WITH(NOLOCK)
> INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> There lots of these and many of them have lots of joins so I'm looking
> for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> the time it takes to add the hint to each table/join. I know that it
> is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> volunteer as the deadlock victim, but this isn't suitable as I need
> the procedure to return it's records.
> Does anybody have a suggestion or am I looking ata couple of days of
> ctrl-v'ing WITH(NOLOCK) everywhere?
> Thanks,
> Liam|||Thanks, that's just what I was looking for.
Liam
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<uvCEm35nDHA.2312@.TK2MSFTNGP12.phx.gbl>...
> How about below?
> SET STRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Liam Weston" <liam_weston@.hotmail.com> wrote in message
> news:5f9a8c3b.0310310227.436b158b@.posting.google.com...
> > I have a number of reporting stored procedures that purely list
> > records and make no changes to the data. I have noticed that some of
> > these SPs are causing blocks so I am adding the WITH(NOLOCK) hint. For
> > a simple example :-
> >
> > Select * from table1 WITH(NOLOCK)
> > INNER JOIN table2 WITH(NOLOCK) ON table1.UID=table2.UID
> > INNER JOIN table3 WITH(NOLOCK) ON table1.AnotherID=table3.AnotherID
> > LEFT OUTER JOIN table4 WITH(NOLOCK) ON table3.ThisID=table4.ThisID
> >
> > There lots of these and many of them have lots of joins so I'm looking
> > for a way to apply WITH(NOLOCK) to the whole procedure and save myself
> > the time it takes to add the hint to each table/join. I know that it
> > is possible to use SET DEADLOCK_PRIORITY LOW, forcing the procedure to
> > volunteer as the deadlock victim, but this isn't suitable as I need
> > the procedure to return it's records.
> >
> > Does anybody have a suggestion or am I looking ata couple of days of
> > ctrl-v'ing WITH(NOLOCK) everywhere?
> >
> > Thanks,
> >
> > Liam