SQL Server IF..ELSE do not show at Windows Application

Question

I have this stored procedure:

exec sp_Defect_B '2013-05-20 00:00:00','2013-05-25 23:59:59'

Which has a IF..ELSE to execute different thing depends on the code given:

alter proc [dbo].[p_Defect_B] (@dtFrom datetime, @dtTo datetime)            
as            
begin  
DECLARE @Total TABLE 
(
  [No] int, TSampel float
)
-- Total
insert into @Total
select 1 as [No], SUM(H.Total) as TSampel from TrxDBHHDr HH
left join TrxDBHdr H on HH.DBNO=H.DBNO
left join ProductType PT on H.ACd=PT.ACd and PT.GCd=1
where HH.Deleted=0 and HH.DBDate between @dtFrom and @dtTo

DECLARE @Defect TABLE 
(
  DefectCd varchar(15),Name varchar(50), Defect float
)
-- Defect
insert into @Defect
select D.DefectCd,DB.Name,sum(coalesce(D.Qty,0)) as Defect from TrxDBHHDr HH
left join TrxDBDtl D on HH.DBNO=D.DBNO
left join ProductType PT on D.acd=PT.ACd and PT.GCd=1
left join DefectBK DB on DB.DefectCd=D.DefectCd
where HH.Deleted=0 and HH.DBDate between @dtFrom and @dtTo
group by D.DefectCd,DB.Name

DECLARE @SubTotal TABLE 
(
  Name varchar(50), Defect float, TSampel float, PDefect float
)
insert into @SubTotal
select D.Name,D.Defect,T.TSampel,D.Defect*100/T.TSampel as PDefect from @Defect D
left join @Total T on T.[No]=1
order by PDefect desc

DECLARE @TotalD TABLE 
(
  [No] int,Defect float
)

insert into @TotalD
select 1, Sum(D.Defect) as Defect from @Defect D

insert into @SubTotal
select 'Total Defect', D.Defect, T.TSampel, D.Defect*100/T.TSampel as PDefect from @TotalD D
left join @Total T on T.[No]=1

select * from @SubTotal
end

I execute the code in SSMS and it worked perfectly. But when I try to use the code in C# Windows application it doesn't get any value.... How is that possible? Did I miss anything?

Only this stored procedure didn't return table value....

I tried using temp table, table variable, they still didn't return table value...

This is the C# Code:

sql= "exec p_Defect_B '2013-05-20 00:00:00','2013-05-25 23:59:59'";

RunQuery qu_data = new RunQuery();
DataTable data = new DataTable();
data = qu_data.OpenAdoQuery(sql,"IP")

This is part of my program of my connection C# to SQL Server

myCon = new OleDbConnection(strCon);
DataTable myData = new DataTable();

myCon.Open();

OleDbDataAdapter myOleAdapter = new OleDbDataAdapter();
myOleAdapter.SelectCommand = new OleDbCommand(sql,myCon);
myOleAdapter.Fill(myData);
myCon.Close();

• All the tables return value in SMSS.
• All table variable show result in SMSS.
• The result didn't show on C# Windows Application using ADOAdapter.
• I tried using Temp Table and Table Variable, didn't work.
• I tried not using IF..ELSE, didn't work.

Answer 1

You should really use the SqlConnection then connecting to SQL Server - and you should execute your stored procedure using a standard SqlCommand - don't use the EXEC.... code.

Try this code:

// setup connection and command
using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
using (SqlCommand cmd = new SqlCommand("dbo.p_Defect_B", conn))
{
    // define command as stored procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // define and set parameter values
    cmd.Parameters.Add("@dtFrom", SqlDbType.DateTime).Value = new DateTime(2013, 5, 20);
    cmd.Parameters.Add("@dtFrom", SqlDbType.DateTime).Value = new DateTime(2013, 5, 25, 23, 59, 59);

    // execute your query
    conn.Open();

    // get a data reader to read the values from the result set
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        // iterate over the result set
        while (rdr.Read())
        {
            // fetch the values - depends on your result set - YOU NEED TO ADAPT THIS!
            var value1 = rdr.GetInt(0);
            var value2 = rdr.GetString(1);
            ......
        }

        rdr.Close();
    }

    conn.Close();
}