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

Please post code for the stored procedure and invocation (both sql and c#) — SQLDiver, Dec 31 '14 at 12:06
You're more likely to get an answer if you post the code you're using when running/calling the stored procedure (both for the dry run and in your C# code) — Neil Mountford, Dec 31 '14 at 12:06
@MarsOne Please provide some more specific information. 1 - Are you using `ADO.NET`, `EF` or something else on `C#` side. 2 - The code that you try to execute. 3 - the error you get. — Leron, Dec 31 '14 at 12:07
Do you have the SET NOCOUNT ON command in your Stored Procedure? — SQLDiver, Dec 31 '14 at 12:16
When you say the call fails, could you elaborate, are you getting an exception, do you just get no data? If you're getting an exception then please post that too. — Neil Mountford, Dec 31 '14 at 12:23
How does the SP return the data? Does it use a select statement, or do you use that return value as its only ouput? — Dirk, Dec 31 '14 at 12:36
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Dec 31 '14 at 12:56
Check to make sure your `SQLConnection` object `conn` is not involved in a transaction, which could be using a retrictive isolation level such as Repeatable Read or Serializable. Also check if you are using `System.Transactions.Transactionscope` anywhere, b/c its default isolation level is Serializable, so you have to be careful when using. Also make certain that you are connecting to the *same* SQL instance and database in your app and when your "dry run" SQL statement. — BateTech, Jan 03 '15 at 16:45

score 7 · Accepted Answer · edited May 23 '17 at 11:58

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:

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?
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

score 1 · Answer 2 · answered Jan 06 '15 at 11:13

1

Start SQL profiler and execute your code. Check hit is coming to profiler. Copy from there and run in editor window to check stats.

answered Jan 06 '15 at 11:13

VSharma

348
2
12

score 0 · Answer 3 · answered Jan 07 '15 at 10:29

Use This

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 
}

Stored Procedure works in DB but not in C# code

3 Answers3