存储过程在DB中工作，但在C#代码中不起作用。

Question

我有一个存储过程，它有3个输入参数。Id和2个VarChar条件。当我尝试运行存储过程时，它可以正常工作，但是当我从我的C#代码调用相同的存储过程时，它会失败。我传递在存储过程的模拟运行中使用的完全相同的参数，但是存储过程一直挂起。

有什么理由发生这种事吗？

我正在使用Server 2008 R2速成版。

这是存储过程的试运行：

EXEC    @return_value = [dbo].[GetAttributes]
        @pi_PId = 95102,
        @pi_returnOnly1stRow = 0,
        @pi_returnExtAttr = 1

SELECT  'Return Value' = @return_value

这是来自C#代码的调用：

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand("GetAttributes", conn);
da.SelectCommand.Parameters.AddWithValue("@pi_PId", 95102); 
da.SelectCommand.Parameters.AddWithValue("@pi_returnOnly1stRow", 0);
da.SelectCommand.Parameters.AddWithValue("@pi_returnExtAttr", 1);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();
da.Fill(ds, "result_name");
DataTable dt = ds.Tables["result_name"];
conn.Close();

编辑：问题返回..。

我以为我已经解决了这个问题，因为这个过程导致了这个问题，但不幸的是，returned.Having说我对这个问题有了更好的理解，我可以看到问题，但不知道是什么原因造成了这个问题。

问题是一样的。如果直接在SQL server上运行，则存储过程运行良好，但在从C#程序调用时无法执行.

我运行了Sp_lock。我使用的存储过程具有Spid：'59‘，锁如下所示。我不知道为什么这些表只有在从c#调用存储过程时才被锁定，而不是在SP试运行期间才被锁定。

59  2   0           0   DB  [ENCRYPTION_SCAN]                   S   GRANT
59  5   1802489500  0   PAG 1:169937                            S   GRANT
59  5   1914489899  0   TAB                                     IS  GRANT
59  5   1898489842  0   TAB                                     IS  GRANT
59  5   1177771253  0   TAB                                     IS  GRANT
59  5   1786489443  0   TAB                                     IS  GRANT
59  5   1802489500  0   TAB                                     IS  GRANT
59  5   1882489785  0   TAB                                     IS  GRANT
59  5   0           0   DB                                      S   GRANT

最新编辑：我还通过在Sp所涉及的每个表上引入NOLOCK来编辑我的SP

我也在这里粘贴存储过程，以供您参考(添加了最新的NOLOCK ).

    @pi_PId                 INT
,   @pi_returnOnly1stRow    BIT
,   @pi_returnExtAttr       BIT
AS

BEGIN 
    IF(@pi_returnOnly1stRow=1)
        BEGIN
            SELECT TOP 1 NULL section_name,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee WITH (NOLOCK)
            JOIN   cds_mvocee header_mvoc WITH (NOLOCK)ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc WITH (NOLOCK) ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod WITH (NOLOCK)ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   ProductVariant revpro WITH (NOLOCK) On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            UNION
            SELECT section_evoc.Text section_name, header_evoc.text Attr_Name, body_evoc.Text Attr_Value, cds_Especee.DisplayOrder
            FROM   cds_Especee WITH (NOLOCK) 
            JOIN   cds_Evocee section_evoc WITH (NOLOCK) ON (cds_Especee.SectID = section_evoc.ID)
            JOIN   cds_Evocee header_evoc WITH (NOLOCK) ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc WITH (NOLOCK) ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod WITH (NOLOCK) ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   ProductVariant revpro WITH (NOLOCK) On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            AND    @pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
    ELSE
        BEGIN
            SELECT NULL section_name ,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee WITH (NOLOCK) 
            JOIN   cds_mvocee header_mvoc WITH (NOLOCK) ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc WITH (NOLOCK) ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod WITH (NOLOCK) ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   productVariant revpro WITH (NOLOCK) On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            UNION
            SELECT section_evoc.Text section_name,header_evoc.text Attr_Name, body_evoc.Text Attr_Value,cds_Especee.DisplayOrder
            FROM   cds_Especee WITH (NOLOCK) 
            JOIN   cds_Evocee section_evoc WITH (NOLOCK) ON (cds_Especee.sectid = section_evoc.id)
            JOIN   cds_Evocee header_evoc WITH (NOLOCK) ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc WITH (NOLOCK) ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod WITH (NOLOCK) ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   productVariant revpro WITH (NOLOCK) On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @pi_PId
            AND    @pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
END

Answer 1

这听起来可能是“参数嗅探”(http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx)的一个问题，这意味着为您的过程缓存的执行计划可以根据过程之前的执行情况为不同的参数值进行优化。对于参数值x，此缓存计划可能是有效的，但对于参数值y则不然。我以前也遇到过同样的问题，当我在SSMS中运行一个查询时，它会立即运行，但是如果我从我的应用程序运行，它就会“挂起”。这是因为当我在SSMS中运行时，查询文本与来自应用程序的查询文本略有不同，因此它使用了不同的缓存执行计划。

解决此问题的方法之一是在proc中创建一个本地作用域变量，这些变量充当过程参数的副本。

我还建议：

1. 如果您没有从UNION的两边返回重复行，那么将UNION替换为UNION ALL，以消除Server检查行中重复值的不必要开销。What is the difference between UNION and UNION ALL?
2. 避免使用WITH(NOLOCK)提示，因为这些提示可能导致数据一致性问题。只有在您完全理解副作用时才使用此提示，并且您的应用程序可以进行脏读取，并且可能会多次返回同一行(When should you use "with (nolock)")。

下面是一个包含上述反馈的例子：

ALTER PROC dbo.usp_YourProcName
    @pi_PId                 INT
,   @pi_returnOnly1stRow    BIT
,   @pi_returnExtAttr       BIT
AS
BEGIN 
    --these local variables are used to address "parameter sniffing" issues which may cause an ineffient plan cache.
    --Use these local variables below instead of the direct parameter values.
    DECLARE @local_pi_PId             INT = @pi_PId
    ,   @local_pi_returnOnly1stRow    BIT = @pi_returnOnly1stRow
    ,   @local_pi_returnExtAttr       BIT = @pi_returnExtAttr
    ;

    IF(@local_pi_returnOnly1stRow=1)
        BEGIN
            SELECT TOP 1 NULL section_name,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee 
            JOIN   cds_mvocee header_mvoc ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc  ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   ProductVariant revpro  On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            UNION ALL
            SELECT section_evoc.Text section_name, header_evoc.text Attr_Name, body_evoc.Text Attr_Value, cds_Especee.DisplayOrder
            FROM   cds_Especee  
            JOIN   cds_Evocee section_evoc  ON (cds_Especee.SectID = section_evoc.ID)
            JOIN   cds_Evocee header_evoc  ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc  ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   ProductVariant revpro  On (revpro.ManufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            AND    @local_pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
    ELSE
        BEGIN
            SELECT NULL section_name ,header_mvoc.text Attr_Name, body_mvoc.Text Attr_Value,cds_mspecee.DisplayOrder
            FROM   cds_mspecee  
            JOIN   cds_mvocee header_mvoc  ON (cds_mspecee.hdrid = header_mvoc.id)
            JOIN   cds_mvocee body_mvoc  ON (cds_mspecee.bodyid = body_mvoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_mspecee.prodid)
            JOIN   productVariant revpro  On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            UNION ALL
            SELECT section_evoc.Text section_name,header_evoc.text Attr_Name, body_evoc.Text Attr_Value,cds_Especee.DisplayOrder
            FROM   cds_Especee  
            JOIN   cds_Evocee section_evoc  ON (cds_Especee.sectid = section_evoc.id)
            JOIN   cds_Evocee header_evoc  ON (cds_Especee.hdrid = header_evoc.id)
            JOIN   cds_Evocee body_evoc  ON (cds_Especee.bodyid = body_evoc.id)
            JOIN   cds_prod  ON (cds_Prod.prodid = cds_especee.prodid)
            JOIN   productVariant revpro  On (revpro.manufacturerSKU=cds_prod.mfpn)
            AND    revpro.ProductID = @local_pi_PId
            AND    @local_pi_returnExtAttr = 1
            ORDER BY section_name,displayorder ASC
        END
END

Answer 2

启动SQL分析器并执行代码。检查命中正在找分析器。从那里复制并在编辑器窗口中运行以检查统计数据。

Answer 3

用这个

SqlConnection con=new SqlConnection("ConnecttionString"); 
con.Open();
SqlCommand cmd = new SqlCommand("ProcedureName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("StoredProcedureParameter", Value);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adp .Fill(dt);
con.Close();
if (dt.Rows.Count > 0)
{
 // get the data table field value here 
}
else
{
// Table is Empty 
}