November 16, 2012 at 8:15 am
Hello,
we have a SP which in turn calls 2 sp's.
because of one sp output data got updated wrongly .
but when i run the sp with necessary paratmeters it is giving proper output.
Note: that SP is not giving wrong output every time.
any suggestions?
Regards
Durai Nagarajan
November 16, 2012 at 11:33 pm
As long as its running successfully,it should not give wrong output.
Just curious,did you used NOLOCK hint anywhere inside SP ?
-----------------------------------------------------------------------------
संकेत कोकणे
November 19, 2012 at 1:17 am
durai nagarajan (11/16/2012)
Note: that SP is not giving wrong output every time.
Here do you mean that with same parameters you are getting different results (sometime right sometmes wrong ) ? any inbult function like getdate() OR RAND() fuunction used inside ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 19, 2012 at 2:04 am
Nolock is not used.
we havent used inbuilt function also.
there is date conversion and comparison between a table column and input parameters.
i forgot to mention it is compared with a encypted column also.
anu suggestion based on above details.
Regards
Durai Nagarajan
November 19, 2012 at 2:15 am
durai nagarajan (11/19/2012)
Nolock is not used.we havent used inbuilt function also.
there is date conversion and comparison between a table column and input parameters.
i forgot to mention it is compared with a encypted column also.
anu suggestion based on above details.
can you post the Sp definition here ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 19, 2012 at 2:25 am
ALTER PROCEDURE SP_Name
( @empCode VARCHAR(10), @date DATETIME )
with encryption
AS
BEGIN
open symmetrickey code part
SELECT dbo.Decrypt(EmpCode), dbo.Decrypt(EmpName),
CASE LeaveType WHEN 'ON DUTY' THEN 'OD'
WHEN 'Paternity' THEN 'CL'
WHEN 'Absconding' THEN 'A'
WHEN 'Breav' THEN 'CL'
ELSE LeaveType
END, CONVERT(VARCHAR, @date, 101), LeaveStatus FROM Leave_Details
WHERE CONVERT(DATETIME, CONVERT(VARCHAR, @date, 101)) BETWEEN FromDate AND ToDate
AND LeaveRequestStatus = 'Authorize'
AND dbo.Decrypt(EmpCode) = @empCode
END
this is the code after masking.
Regards
Durai Nagarajan
November 19, 2012 at 3:00 am
durai nagarajan (11/19/2012)
WHERE CONVERT(DATETIME, CONVERT(VARCHAR, @date, 101)) BETWEEN FromDate AND ToDate
this is the code which is creating problem as its not using the index for search (if any ) , better to make the passed parameters according to the column compatible data. see this http://www.sqlusa.com/bestpractices/substring/
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 19, 2012 at 3:28 am
thanks, will check
just a doubt this will have to slow down my query why my output has to vary?
Regards
Durai Nagarajan
November 19, 2012 at 3:34 am
durai nagarajan (11/19/2012)
thanks, will checkjust a doubt this will have to slow down my query why my output has to vary?
your parameter to this sp is @date / So are you giving same values whenever u are getting different output ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 19, 2012 at 3:35 am
No, for some empcode and date combination the output is right for some it is wrong.
Regards
Durai Nagarajan
November 19, 2012 at 4:36 am
durai nagarajan (11/19/2012)
No, for some empcode and date combination the output is right for some it is wrong.
have you try to check or verify the data with the direct sql query ??
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 19, 2012 at 5:35 am
yes, i have even tried it with the sp with parameters and checked the sp which is calling this also.
i cant find any issue as most of them works fine only some of them got in to wrong data.
Regards
Durai Nagarajan
November 19, 2012 at 10:27 pm
durai nagarajan (11/19/2012)
only some of them got in to wrong data.
There has to be some thing in query itself ..you are overlooking somethng
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply