DB gurus & gurettes!Anybody see anything inefficient about this sproc?

Question

DB gurus & gurettes!Anybody see anything inefficient about this sproc?

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
October 17, 2008 at 9:42 am

#92657

CREATE PROCEDURE [dbo].[svg_RiskGetCardLocationByIso]
declare @CardNum varchar(100),
declare @DateFrom datetime,
declare @DateTo datetime,
@IsoNum int,
@CardNumDES3 varchar(100) ='0000000000000000',
@username varchar(100)=''
AS
set @CardNumDES3= dbo._Func_DecodeString(@CardNum,'andreso')
set @CardNumDES3= VimasBatches.dbo._Func_EncodeString(@CardNumDES3)
SELECT Mid, DBA, TransDate, /*CASE WHEN TransCode IN('06') THEN -TransAmt1 ELSE TransAmt1 END AS*/ TransAmt1
FROM BatchTrans WITH (NOLOCK) INNER JOIN BatchHeader WITH (NOLOCK) ON BatchTrans.TransID=BatchHeader.TransID
INNER join Locate WITH (NOLOCK) ON BatchHeader.MerchIntID=Locate.IntMid
INNER join MerSum WITH (NOLOCK) ON Locate.IntMid=MerSum.IntMid
WHERE DateCreated between CONVERT(varchar(8), @DateFrom,1) AND CONVERT(varchar(8), @DateTo,1)
AND (CardNum=@CardNum or CardNum=@CardNumDES3)
and (
((isnull(MerSum.alternativeisonum,0)=0 and
(MerSum.IsoNum=@IsoNum or MerSum.IsoNum in (select isonum from multipleuserreference where username=@username)))
or
(isnull(MerSum.alternativeisonum,0)<>0 and
(MerSum.alternativeisonum=@IsoNum or MerSum.alternativeisonum in (select isonum from multipleuserreference where username=@username))))
)
----------------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What's the question?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 2

Execution plan?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

CREATE PROCEDURE [dbo].[svg_RiskGetCardLocationByIso]

declare @CardNum varchar(100),

declare @DateFrom datetime,

declare @DateTo datetime,

@IsoNum int,

@CardNumDES3 varchar(100) ='0000000000000000',

@username varchar(100)=''

AS

...

Remove the word declare from the list of parameters. It is not valid at that point and should be:

CREATE PROCEDURE [dbo].[svg_RiskGetCardLocationByIso]

@CardNum varchar(100),

@DateFrom datetime,

@DateTo datetime,

@IsoNum int,

@CardNumDES3 varchar(100) = '0000000000000000',

@username varchar(100) = ''

AS

BTW - if you had taken the time to format the procedure (just a little bit) - you probably would have seen this right away yourself.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 4

I need to run Update Stats. I failed last nite and I think that's the problem all. Thanks!