Stored Procedure works in DB but not in C# code

Question

I have a stored procedure which has 3 input parameters. Id, and 2 VarChar conditions. It works fine when I do a dry run of the stored procedure, but when I call the same stored procedure from my C# code, it fails. I pass the exact same parameters which I use in the dry run of the stored procedure, but the stored procedure keeps hanging.

Is there any reason for this to happen?

I am using SQL Server 2008 R2 express edition.

This is the dry run of the stored procedure:

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

SELECT  'Return Value' = @return_value

This is the call from the C# code:

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();

EDIT: The problem Returns....

I thought i had solved this issue by killing the process's which caused this issue but unfortunately it has returned.Having said that i have a better understanding of the issue and i can see the problem, but not sure what causes this.

The problem is the same. Stored Procedure runs fine if run directly on SQL server but fails to execute when called from C# program....

I ran Sp_lock. The stored procedute i used has the Spid: '59' for which the locks are shown as below. I do not know why these tables get locked only when the Stored procedure is called from c# and not when during a dry run of the 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

LATEST EDIT: I have also edited my Sp by introdrucing WITH NOLOCK ON each of the tables involved in the SP

I am also pasting the stored procedure here for your reference(WITH LATEST NOLOCK added)....

    @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

This sounds like it could be an issue with "parameter sniffing" (http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx), which means that the execution plan that is cached for your procedure could be optimized for different parameter values based on prior execution of the procedure. This cached plan could be efficient for parameter value x, but not for parameter value y. I have run into this same issue before, where when I run a query in SSMS it runs immediately, but if I run from my app, it "hangs". This is because the query text when i run in SSMS is slightly different than the query text that comes from the app, so it uses a different cached execution plan.

One workaround to fix this issue is to create a locally scoped variables within your proc, which act as a copy of your procedure parameters.

I would also recommend:

1. If you do not have duplicate rows returned from each side of the UNION, then replace the UNION with UNION ALL to remove the un-needed overhead of SQL Server checking for duplicate values in rows. What is the difference between UNION and UNION ALL?
2. Avoid using WITH(NOLOCK) hints because these can lead to data consistency issues. Only use this hint if you fully understand the side effects, and your application is OK with dirty reads and with possibly having the same row returned more than once (When should you use "with (nolock)").

Here is an example incorporating the feedback mentioned above:

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