SQL Server -- 解決存儲過程傳入參數作為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

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