如何在存储过程中使用OUTPUT参数并在c＃代码中进行检索？ [英] how to use OUTPUT parameter in stored procedure and retrive it in c# code?

问题描述

任何人都可以告诉我如何在sp中使用输出参数并在c＃代码中进行检索。我的sp如下。

Can any one tell me how to use output parameter in sp and retrive it in c# code.My sp is as follow.

ALTER PROCEDURE [dbo].[ssp_AgriShowdata]
@temp float,
@lpH INT OUTPUT,
@lEC INT OUTPUT,
@lTexture INT OUTPUT,
@lNitrogen INT OUTPUT,
@lPhosphorus INT OUTPUT,
@lPotassium INT OUTPUT
AS
BEGIN
Select C.c_SerialNo,C.c_FirstName,C.c_MiddleName,C.c_LastName,C.c_addr,C.c_Taluka,C.c_SurveyNo,C.c_ReportNumber,C.a_pH,C.a_EC,C.a_Texture,C.a_Nitrogen,C.a_Phosphorus,C.a_Potassium,C.s_Magnessium,C.s_Calcium,C.s_Sulphur,C.m_Zinc,C.m_Iron,C.m_Manganese,C.m_Copper,C.m_Boron,C.c_Date,@lpH=C.l_pH /*as '+@lpH+'*/ ,C.l_EC,C.l_Texture,C.l_Nitrogen,C.l_Phosphorus,C.l_Potassium,R.r_Crop,R.r_Nitrogen,R.r_Phosphorus,R.r_Potassium,R.r_DAP,R.r_Urea,R.r_PotashMOP,R.r_ZincSulphate,R.r_IronSulphate,R.r_ManganeseSulphate,R.r_CopperSulphate,R.r_Borax,R.r_GreenManure,R.r_FYMCompost,R.r_LimeGypsum,R.r_AfterEveryYears FROM Cultivator_tbl C LEFT OUTER JOIN Recommendation_tbl  R on C.Cid=@temp AND R.Did=@temp ORDER BY C.c_FirstName ASC,R.Rid ASC ;
END

我写的时候
@ lpH = C .l_pH 它显示我的一些错误 C.c_SerialNo

in the sp when i write @lpH=C.l_pH it shows me some error for C.c_SerialNo

推荐答案

请，阅读这些：

如何：创建并执行返回单个的SQL语句价值 [ ^ ]

如何：执行返回单个值的存储过程 [ ^ ]

如何：创建并执行返回行的SQL语句 [ ^ ]

如何：执行返回行的存储过程 [ ^ ]



你是确保您的查询为每个outp参数返回单个值？我建议你使用返回行的SP。

Please, read these:
How to: Create and Execute an SQL Statement that Returns a Single Value[^]
How to: Execute a Stored Procedure that Returns a Single Value[^]
How to: Create and Execute an SQL Statement that Returns Rows[^]
How to: Execute a Stored Procedure that Returns Rows[^]

Are you sure that your query returns single value for each outp parameter? I would suggest you to use SP that returns rows.

你的存储过程有错误。我不认为在SQL Server中允许同时分配和返回结果集。按照以下顺序更改您的SP并进行调试。

Your stored procedure has errors. I dont think assigment and returning result set at the same time is allowed in SQL Server. Change your SP in following order and debug.

select @lpH=C.l_pH /*, rest of the parameters assigment */
FROM Cultivator_tbl C LEFT OUTER JOIN Recommendation_tbl  R on C.Cid=@temp AND R.Did=@temp ORDER BY C.c_FirstName ASC,R.Rid ASC ;

如果你正在使用linq到sql类，你可以将结果存储在临时变量中

if you're using linq to sql classes, you can store the result in a temporary variable

...

try
{
   var res = {placedatacontextvariablehere}.ssp_AgriShowdata({sp parameters});

   //consume the result set

   //if it's a list
   foreach(var r in res)
   { 
      // to-do
      ... r. ...
   }
   //or any other looping mechanism you prefer
   
   

 } catch {}

这篇关于如何在存储过程中使用OUTPUT参数并在c＃代码中进行检索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！