执行存储过程

• Z时代
• 2024-01-10
• 分类：问答

如何在ASP.net或c＃中执行此存储过程" title="存储过程">存储过程？以及如何传递参数@batch日期以便可以基于batch_date操作输出？执行存储过程

ALTER PROCEDURE [dbo].[inv_check] @batch_date datetime AS BEGIN 
CREATE TABLE #temp1 
(batch_date datetime,part_no varchar(25),Tot_Tid_Count_Qty int,Number_of_tid int,Updated_DT int) 
INSERT INTO #temp1 
SELECT batch_date,part_no,0,count(tid),0 
FROM dbo.tnx_tid_InvCheck_Details 
where batch_date = @batch_date 
Group by part_no,batch_date 
order by part_no 
UPDATE #temp1 
SET Updated_DT = isnull(d.Updated_DT,0) 
--select i.part_no,i.Number_of_tid, isnull(d.Updated_DT,0) 
FROM #temp1 p 
LEFT OUTER JOIN 
    (SELECT part_no,COUNT(LastUpdate_DT)as Updated_DT 
    FROM dbo.tnx_tid_InvCheck_Details 
    Where NOT LastUpdate_DT IS NULL and batch_date = @batch_date 
    Group by part_no,batch_date) d on p.part_no=d.part_no 
DECLARE @sql int 
DECLARE @sql1 int 
SELECT @sql1 = count(part_no) 
FROM #temp1 
SELECT @sql = count(part_no) 
FROM #temp1 
WHERE number_of_tid= Updated_DT 
DELETE #temp1 
CREATE TABLE #temp2 
(batch_date datetime,part_no varchar(25),kb02_qty int,sum_count_qty int,variance int) 
INSERT INTO #temp2 
SELECT batch_date,part_no,kb02_qty,0,0 
FROM dbo.tnx_tid_InvCheck_Master 
where NOT kb02_qty IS NULL AND batch_date = @batch_date 
ORDER BY part_no 
UPDATE #temp2 
SET sum_count_qty = isnull (q.sum_count_qty,0) 
--select i.part_no,i.Number_of_tid, isnull(d.Updated_DT,0) 
FROM #temp2 t 
LEFT OUTER JOIN 
    (SELECT part_no,sum(count_qty) as sum_count_qty 
    FROM dbo.tnx_tid_InvCheck_Details 
    Where NOT LastUpdate_DT IS NULL AND batch_date = @batch_date 
    Group by part_no) q on t.part_no= q.part_no 
UPDATE #temp2 
SET variance=(kb02_qty - sum_count_qty) 
DECLARE @parts_having_variance int   
DECLARE @negative_variance_qty int 
DECLARE @Negative_Variance_Parts int 
DECLARE @Positive_Variance_Qty int 
DECLARE @Positive_Variance_Parts int 
SELECT @parts_having_variance = COUNT(part_no) 
FROM #temp2 
WHERE NOT variance = 0 
SELECT @negative_variance_qty = sum(variance) 
FROM #temp2 
Where variance < 0 
SELECT @Negative_Variance_Parts = COUNT(variance) 
FROM #temp2 
Where variance < 0 
SELECT @Positive_Variance_Qty = sum(variance) 
FROM #temp2 
Where variance > 0 
SELECT @Positive_Variance_Parts = COUNT(variance) 
FROM #temp2 
Where variance > 0 
INSERT INTO #temp1 
SELECT batch_date,part_no,0,COUNT(tid)as Number_of_tid,0 
FROM dbo.tnx_tid_InvCheck_Details 
WHERE batch_date = @batch_date 
Group by part_no,batch_date 
order by part_no 
UPDATE #temp1 
SET Updated_DT = isnull(d.Updated_DT,0) 
FROM #temp1 p 
LEFT OUTER JOIN 
    (SELECT part_no,COUNT(LastUpdate_DT)as Updated_DT 
    FROM dbo.tnx_tid_InvCheck_Details 
    Where NOT LastUpdate_DT IS NULL AND batch_date = @batch_date 
    Group by part_no,batch_date) d on p.part_no=d.part_no 
    DECLARE @tid_count int 
    SELECT @tid_count=SUM(Number_of_tid) 
    FROM #temp1 
    WHERE Number_of_tid = Updated_DT 
UPDATE #temp1 
SET Tot_Tid_Count_Qty = (e.Tot_Tid_Count_Qty) 
FROM #temp1 p 
LEFT OUTER JOIN 
(SELECT part_no,SUM(count_qty)AS Tot_Tid_Count_Qty 
    FROM dbo.tnx_tid_InvCheck_Details 
    WHERE batch_date = @batch_date 
    Group by part_no,batch_date) e on p.part_no=e.part_no 
    DECLARE @tid_count_qty int 
    SELECT @tid_count_qty = SUM(Tot_Tid_Count_Qty) 
    FROM #temp1 
    WHERE Number_of_tid = Updated_DT 
SELECT ' Parts Counted ' AS CRITERIA , LTRIM(RTRIM(CONVERT(CHAR,@sql))) + '/' + LTRIM(RTRIM(CONVERT(CHAR,@sql1))) AS QTY 
UNION 
SELECT ' Parts Having Variance ' AS CRITERIA ,LTRIM(RTRIM(CONVERT(CHAR,@parts_having_variance))) AS QTY 
UNION 
SELECT ' Total tid Count ' AS CRITERIA ,LTRIM(RTRIM(CONVERT(CHAR,@tid_count))) AS QTY 
UNION 
SELECT ' Total tid Count Qty ',LTRIM(RTRIM(CONVERT(CHAR,@tid_count_qty))) AS QTY 
UNION 
SELECT ' Total Negative Variance Qty ' AS CRITERIA ,LTRIM(RTRIM(CONVERT(CHAR,@negative_variance_qty))) AS QTY 
UNION 
SELECT ' Negative Variance Parts ' AS CRITERIA ,LTRIM(RTRIM(CONVERT(CHAR,@Negative_Variance_Parts))) AS QTY 
UNION 
SELECT ' Total Positive Variance Qty ' AS CRITERIA ,LTRIM(RTRIM(CONVERT(CHAR,@Positive_Variance_Qty))) AS QTY 
UNION 
SELECT ' Positive Variance Parts ' AS CRITERIA ,LTRIM(RTRIM(CONVERT(CHAR,@Positive_Variance_Parts))) AS QTY 
Drop table #temp1 
Drop table #temp2 
END 

我想这ASP.net代码...它不工作..... u人可以帮助我解决这个code..tq ...

 using System; 
     using System.Collections.Generic; 
     using System.Linq; 
     using System.Web; 
     using System.Web.UI; 
     using System.Web.UI.WebControls; 
     using System.Data; 
     using System.Data.SqlClient; 
     namespace new1 
     { 
      public partial class _Default : System.Web.UI.Page 
     { 
    protected void Page_Load(object sender, EventArgs e) 
    { 
     string str = "Data Source = kmsiis010;Initial Catalog =db_IntelliMat;Integrated Security=True"; 
     SqlConnection conn = new SqlConnection(str); 
     SqlCommand cmd = new SqlCommand("inv_check", conn); 
     SqlDataAdapter da = new SqlDataAdapter(); 
     da.SelectCommand = cmd; 
     DataSet ds = new DataSet(); 
     da.Fill(ds, "dbo.tnx_tid_InvCheck_Details"); 
     this.GridView1.DataSource = ds; 
     this.GridView1.DataBlind(); 
     } 
     } 
     } 

回答：

回答：

try 
{ 
    using (SqlConnection connection = new SqlConnection(connectionString)) 
    { 
    using(SqlCommand command = new SqlCommand("inv_check", connection)) 
    { 
     command.CommandType = CommandType.StoredProcedure; 
     command.Parameters.Add("@batch_date", SqlDbType.DateTime).Value = DateTime.Now; // or the variable which holds datetime 
     connection.Open(); 
     return command.ExecuteNonQuery(); 
    } 
    } 
} 
catch (SqlException ex) 
{ 
    Response.Write("SQL Error" + ex.Message.ToString()); 
} 

以上是 执行存储过程 的全部内容， 来源链接： utcz.com/qa/262458.html