how to get out put from stored procedure by giving input from user

Posted by Shanky11 under ASP.NET on 9/24/2013 | Points: 10 | Views : 1418 | Status : [Member] | Replies : 6
create procedure sp_orders
(
@totalamount float,
@oid int out
)
As
Begin
insert into orders(totalamt)values(@totalamount)
Declare @orderid int;
select @oid= IDENT_CURRENT('orders')

End


table name orders
oid(identity),totalamt(float)
after insert value in table i need current identity through out parameter stored procedure




Responses

Posted by: aswinialuri-19361 on: 9/24/2013 [Member] Starter | Points: 25

Up
0
Down
if you need out parameter in stored procedure you have to write in this way

create procedure sp1(@userid int ,@username nvarchar(50),@designation nvarchar(50))
as
begin
select @username=username,@designation=designation from tablename where userid=@userid
end



here userid is input
and username, designation is output

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
create procedure usp_orders 

(
@totalamount float,
@oid int out
)
As
Begin
insert into orders(totalamt)values(@totalamount)
Declare @orderid int;
select @oid= IDENT_CURRENT('orders')
End
GO


--SQL Server execution of Stored procedure which is having OUT param

DECLARE @id INT
EXEC usp_orders 100, @id OUT
SELECT @id


-- ASP .NET C# code for getting OUT param from Stored Procedure
int Id = 0;

SqlCommand cmd = new SqlCommand("usp_orders", con);
cmd.Parameters.Add("@Output", SqlDbType.Int);
cmd.Parameters["@Output"].Direction = ParameterDirection.Output;
Id = (int)cmd.Parameters["@Output"].Value;




Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Using Entity Framework:
http://code.msdn.microsoft.com/How-to-retrieve-output-e85526ba

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Simple example to capture RETRUN value and OUT value from stored procedure:

How to capture OUT and RETURN parameter values from a stored procedure in .Net.



Well, I’ll let the example speak for itself.



First create a stored procedure, this just take an in parameter and an out parameter, it declares a local variable that will be used as the return value.



create procedure ParamExample (@inParam int, @outParam int out)

as

begin

declare @retParam int

set @outParam = @inParam * 2

set @retParam = @inParam * 4

return @retParam

end



and test it:


declare @in int

declare @out int

declare @ret int



set @in = 100

set @out = 0



exec @ret = ParamExample @in, @out out

select @out as o, @ret as r



This should simply returns 200 for out parameter and 400 for the return.



Now, create a console application (C# in my case) replace the Main method with this.

I’ve used two approaches, one verbose and a shorter one, they both to the same thing however.


string cs = @"Data Source=.\sqlexpress;Initial Catalog=Repros;Integrated Security=SSPI";

string sql = "ParamExample";

try

{

// Verbose approach

using (SqlConnection con = new SqlConnection(cs))

{

con.Open();



// Create the command, setting the command text to be the stored procedure name

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandText = sql;

// Also need to specify that this is a stored procedure command (default is Text)

cmd.CommandType = System.Data.CommandType.StoredProcedure;



// Create the input paramenter, set the properites and add to command.

SqlParameter inParam = new SqlParameter();

inParam.SqlDbType = System.Data.SqlDbType.Int;

inParam.ParameterName = "@inParam";

inParam.Direction = System.Data.ParameterDirection.Input;

inParam.Value = 100;

cmd.Parameters.Add(inParam);



// Create the out paramenter, set the properites and add to command).

SqlParameter outParam = new SqlParameter();

outParam.SqlDbType = System.Data.SqlDbType.Int;

outParam.ParameterName = "@outParam";

outParam.Value = 100;

outParam.Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add(outParam);



// Create the return paramenter, set the properites and add to command).

SqlParameter retParam = new SqlParameter();

retParam.SqlDbType = System.Data.SqlDbType.Int;

retParam.ParameterName = "@retParam";

retParam.Direction = System.Data.ParameterDirection.ReturnValue;

cmd.Parameters.Add(retParam);



// Execute the command

cmd.ExecuteNonQuery();



// Get the values

int retval = (int)cmd.Parameters["@retParam"].Value;

int outval = (int)cmd.Parameters["@outParam"].Value;

Console.WriteLine("Return value: {0}, Out value: {1}", retval, outval);

// or get them directly from the parameter variable itself.

Console.WriteLine("Return value: {0}, Out value: {1}", retParam.Value, outParam.Value);

con.Close();

}



// Short approach

using (SqlConnection con = new SqlConnection(cs))

{

con.Open();

// Create the command, setting the command text to be the stored procedure name

SqlCommand cmd = new SqlCommand(sql, con);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

// Create the input/output/return paramenter

cmd.Parameters.AddWithValue("@inParam", 100).Direction = System.Data.ParameterDirection.Input;

cmd.Parameters.Add("@outParam", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add("@retParam", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

// Execute the command

cmd.ExecuteNonQuery();



// Get the values

int retval = (int)cmd.Parameters["@retParam"].Value;

int outval = (int)cmd.Parameters["@outParam"].Value;



Console.WriteLine("Return value: {0}, Out value: {1}", retval, outval);

con.Close();

}

}

catch (SqlException se)

{

Console.WriteLine(se);

}


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Shanky,

You opened more than 20 threads... If you got solution/help from those answers/replies please mark those as answered so that others can get help from those resolved threads....

To mark a reply as answer click on "Mark As Answer" link below

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/26/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi shanky11!,
Click on "Mark As Answer " for posts which help you a lot... Do not forgot to mark as answered....
Behind Reason: Any one looking for same issue can find out the exact and quick solution by accessing the resolved posts...

It helps others too...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Shanky11, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response