title: Stored Procedure works in DB but not in C# code
body: <p>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.</p>
  
  <p>Is there any reason for this to happen?</p>
  
  <p>I am using SQL Server 2008 R2 express edition.</p>
  
  <p>This is the dry run of the stored procedure:</p>
  
  <pre><code>EXEC    @return_value = [dbo].[GetAttributes]
          @pi_PId = 95102,
          @pi_returnOnly1stRow = 0,
          @pi_returnExtAttr = 1
  
  SELECT  'Return Value' = @return_value
  </code></pre>
  
  <p>This is the call from the C# code:</p>
  
  <pre><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();
  </code></pre>
  
  <p><strong>EDIT:</strong> The problem Returns....</p>
  
  <p>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.</p>
  
  <p>The problem is the same. Stored Procedure runs fine if run directly on SQL server but fails to execute when called from C# program....</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>LATEST EDIT:</strong> I have also edited my Sp by introdrucing WITH NOLOCK ON each of the tables involved in the SP</p>
  
  <p>I am also pasting the stored procedure here for your reference(WITH LATEST NOLOCK added)....</p>
  
  <pre><code>    @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
  </code></pre>