how to use OUTPUT parameter in stored procedure and retrive it in c# code?

Question

0.00/5 (No votes)

See more:

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

SQL

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

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

Posted 11-Aug-13 21:15pm

Rakhil Naik

Updated 11-Aug-13 21:21pm

Maciej Los

v2

Add a Solution

3 solutions

Solution 1

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.

Posted 11-Aug-13 21:25pm

Maciej Los

Comments

Rakhil Naik 12-Aug-13 3:28am

i hv 2 return @lpH,@lEC,@lTexture,@lNitrogen,@lPhosphorus,@lPotassium
can u tell me how to assign selected value from select query into those OUTPUT variable declared above?

Maciej Los 12-Aug-13 3:35am

Have you read my answer and articles? No! Please, read it and then ask me again.

Solution 2

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.

SQL

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 ;

Posted 11-Aug-13 22:13pm

_Asif_

Comments

Rakhil Naik 12-Aug-13 5:33am

thanks
Can u tell me how to get those value in c# code?
SqlParameter lpH = new SqlParameter("@lpH", SqlDbType.Int);
lpH.Direction = ParameterDirection.Output;
cmd.Parameters.Add(lpH);
In the above code when i execute i get @lph as a value in lph.Can u help me out with this?

_Asif_ 12-Aug-13 6:21am

After execution of the SP do this

int returnValue = (int)(Command1.Parameters["lpH"].Value);

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 9549287 · Accepted Answer · 2013-08-13T09:11:00

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

C#

...

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 {}