解決存儲過程傳入參數作為sql語句條件值時，執行阻塞問題

成本核算程式執行某個存儲過程一直阻塞，排查發現類似以下語句阻塞：

select 
    tbl1.product_id,sum(isnull(tbl1.qty,0) * isnull(tbl2.unit_other_cost,0)) as other_cost
from tbl1.p_id=@pId and tbl1.if_stock=0
    and exists(select 1 from tbl3 inner join tbl4 on tbl3.c1=tbl4.c2 where tbl4.c3=0 and tbl3.p_id=tbl1.p_id and tbl3.product_id=tbl1.product_id)
group by tbl1.product_id           

其中參數@pId是存儲過程的傳入參數。測試發現将條件中的@pId改成具體的值，直接執行SQL語句也會阻塞，但是加上變量定義就不會了：

declare @newpId int
set @newpId=99
select 
    tbl1.product_id,sum(isnull(tbl1.qty,0) * isnull(tbl2.unit_other_cost,0)) as other_cost
from tbl1.p_id=@newpId and tbl1.if_stock=0
    and exists(select 1 from tbl3 inner join tbl4 on tbl3.c1=tbl4.c2 where tbl4.c3=0 and tbl3.p_id=tbl1.p_id and tbl3.product_id=tbl1.product_id)
group by tbl1.product_id           

最終決定在存儲過程裡重新定義個變量，指派為傳入參數，将重新定義的變量作為條件值，問題解決。

declare @newpId int
set @newpId=@pId
select 
    tbl1.product_id,sum(isnull(tbl1.qty,0) * isnull(tbl2.unit_other_cost,0)) as other_cost
from tbl1.p_id=@newpId and tbl1.if_stock=0
    and exists(select 1 from tbl3 inner join tbl4 on tbl3.c1=tbl4.c2 where tbl4.c3=0 and tbl3.p_id=tbl1.p_id and tbl3.product_id=tbl1.product_id)
group by tbl1.product_id           

同樣的程式、存儲過程其它工廠核算時沒有問題，即其它資料庫沒有出現阻塞，隻在這個資料庫阻塞，可能跟資料庫設定有關，原理需要再研究。