0

when i tried to execute this stored procedure it is giving me timeout expired error here is my stored procedure

ALTER procedure [dbo].[sp_rptExpMed]
(
  @Stocname as varchar(100),
  @date varchar(40),
  @Mode int
)
as
begin
if @Mode=1
    begin
    select DISTINCT  Em.*,M.DrugName,m.Category
    --,rate 
    from ExpiryMed Em inner join medicinaldrugs M on Em.Drugid=M.drugId
    LEFT join Purchase22011_2012 P on em.DrugID = P.ItemID and Em.batch=p.BatchNo
    order by M.DrugName,Em.Batch
    end
if @Mode=2
    begin
    select DISTINCT Em.*,M.DrugName,m.Category
    --,rate
     from ExpiryMed Em inner join medicinaldrugs M on Em.Drugid=M.drugId
    LEFT join Purchase22011_2012 P on em.DrugID = P.ItemID and Em.batch=p.BatchNo
    where ExpDate<@date
    order by M.DrugName,Em.Batch
    end
end

what is wrong in this. can anyone suggest me a way to avoid this timeout expired error

Sony
  • 7,136
  • 5
  • 45
  • 68

1 Answers1

3

There could be the case that the table(s) you are querying from in your procedure hold a lock due to open/uncommitted transaction on them. Try using a NOLOCK table hint along with the table name like

select DISTINCT  Em.*,M.DrugName,m.Category
from ExpiryMed Em WITH (NOLOCK)
inner join medicinaldrugs M WITH (NOLOCK) on Em.Drugid=M.drugId
LEFT join Purchase22011_2012 P WITH (NOLOCK) on em.DrugID = P.ItemID 
and Em.batch=p.BatchNo
order by M.DrugName,Em.Batch

You can as well set the transaction isolation level to READ UNCOMMITTED

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • thanks for your time, but still it is giving me the same error – Sony Jul 17 '14 at 04:58
  • Strange and shouldn't be. You will have to use `NOLOCK` for all your quries in procedure (OR) set the transaction isolation level globally. Go through the linked MSDN doc to see how. – Rahul Jul 17 '14 at 05:19
  • as you said i set the transaction isolation globally and it worked – Sony Jul 17 '14 at 09:07