December 7, 2011 at 8:53 am
Hi, wondering if anyone can help? I'm in SS 2008 and have a stored procedure. In it, there is a varaible that's declared, and called "@Office". I need to modify this stored proc to include an IF statement like this right at the beginning before processing any code:
IF @office NOT IN ('12', '13', '14)
THEN
execute the rest of the code.
I've tried adding this in before my "BEGIN" statement but it's not working
Any help would be greatly appreciated....thanks!
ALTER PROCEDURE [dbo].[proc_GetOfficeData]
@office Char (5),
@customerNumber CHAR(10)
AS
/********************************************************************************
File:
Name: dbo.proc_GetOfficeData
*********************************************************************************/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT
December 7, 2011 at 8:56 am
First, why READ UNCOMMITTED? Are you sure that the data anomalies are acceptable?
Your code seems to have been cut off, so can't see where you would put the if.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2011 at 9:02 am
Would want to do something like this
CREATE PROC ............ (@Office CHAR(5),.................)
AS
IF @office NOT IN ('12','13','14')
BEGIN
SELECT
'Office is not 12,13 or 14'
END
ELSE
BEGIN
SELECT
'Office is either 12,13 or 14'
END
But going back to what Gail said, can you afford to have dirty reads? Do you want to read the data in an inconsistant state?
December 7, 2011 at 9:07 am
Pitfalls of Nolock (readuncommitted).
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
December 7, 2011 at 9:47 am
Hi, thanks for the notes; much appreciated! I don't know why the READ Uncommited was added. I inherited the maintenance of these stored procs from someone else. I can ask around to see if that can be changed mentioning the suggestions here...
thanks!
December 7, 2011 at 12:27 pm
DucatiRider (12/7/2011)
Hi, thanks for the notes; much appreciated! I don't know why the READ Uncommited was added. I inherited the maintenance of these stored procs from someone else. I can ask around to see if that can be changed mentioning the suggestions here...thanks!
I've inherited a couple of systems where READ UNCOMMITED and NOLOCK were used extensively. When I asked around I found in both cases that 'It has been done like that for years - it's supposed to cut down on deadlocks' or 'it makes the queries run much faster' were the most common responses.
I'd be interested in what you find out after asking around.
Todd Fifield
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply