How to call a stored procedure and return a value?
You say @alphaVar
is varchar(10)
. In that case you need to use an output parameter as below. Return
can only be used for integer types in stored procedures.
CREATE PROCEDURE rnd_STR
@Length int,
@alphaVar varchar(10) OUTPUT
AS
BEGIN
SET @alphaVar = 'blah'
/* Rest of procedure body*/
END
GO
DECLARE @alphaVar varchar(10)
EXEC rnd_STR 10, @alphaVar output
SELECT @alphaVar
Alternatively you could use a scalar UDF rather than a stored procedure.
Calling stored procedure with return value
You need to add a ReturnValue
-direction parameter to the command:
using (SqlConnection conn = new SqlConnection(getConnectionString()))
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = parameterStatement.getQuery();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SeqName", "SeqNameValue");
// @ReturnVal could be any name
var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
conn.Open();
cmd.ExecuteNonQuery();
var result = returnParameter.Value;
}
Setting the parameter's direction to ParameterDirection.ReturnValue
instructs the SqlCommand
to declare it as a variable and assign the stored procedure's return value to it (exec @ReturnValue = spMyProcedure...
), exactly like you would write it in SQL.
Return value from one stored procedure to another
Add
@Status int OUTPUT
in the pro_ForeignKeyCheck
so it starts with
CREATE PROCEDURE dbo.pro_ForeignKeyCheck1
@tableName VARCHAR(100),
@columnName VARCHAR(100),
@idValue int,
@Status int OUTPUT
and at the end of it did as follow
--select coalesce(@fkFound,0)
select @Status = coalesce(@fkFound,0)
--return 0
stop the last to line and add new one
In the other stored procedure, call it as follows
EXEC pro_ForeignKeyCheck1 'tb_M_admin','admin_id', 0 ,@exit output
select @exit
and now the return value will be used.
Thanks to all
Getting return value from stored procedure - C#
Expanding on what Andrei has said you should really use an OUTPUT parameter to see the number of rows affected, return value is only used to see the success/failure of the operation:
A stored procedure with an output parameter would look something like....
ALTER proc [Core].[up_ExternalTradeInsert]
@ExecID char(16),
@SecondaryExecID char(16),
@SecurityID int,
@SecurityIDSource int,
@LastQty int,
@LastPx decimal(12, 6),
@TransactTime datetime2(3),
@Side bit, --0 Sell 1-Buy
@OrderID char(16),
@ClOrdID char(20),
@Account int,
@SenderId int,
@RowCount INT OUTPUT
as
begin
set nocount on
insert Core.ExternalTrade(ExecID, SecondaryExecID, SecurityID, SecurityIDSource, LastQty, LastPx, TransactTime, Side, OrderID, ClOrdID, Account)
values (@ExecID, @SecondaryExecID, @SecurityID, @SecurityIDSource, @LastQty, @LastPx, @TransactTime, @Side, @OrderID, @ClOrdID, @Account)
SET @RowCount = @@rowcount;
end
And you C# code would look something like.....
using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["Connection"]))
{
conn.Open();
SqlCommand cmd = new SqlCommand("Core.up_ExternalTradeInsert", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter("@ExecID", execID));
cmd.Parameters.Add(new SqlParameter("@SecondaryExecID", secondaryExecID));
cmd.Parameters.Add(new SqlParameter("@SecurityID", securityID));
cmd.Parameters.Add(new SqlParameter("@SecurityIDSource", securityIDSource));
cmd.Parameters.Add(new SqlParameter("@LastQty", lastQty));
cmd.Parameters.Add(new SqlParameter("@LastPx", lastPx));
cmd.Parameters.Add(new SqlParameter("@TransactTime", transactTime));
cmd.Parameters.Add(new SqlParameter("@Side", side));
cmd.Parameters.Add(new SqlParameter("@OrderID", orderID));
cmd.Parameters.Add(new SqlParameter("@ClOrdID", clOrdID));
cmd.Parameters.Add(new SqlParameter("@Account", account));
cmd.Parameters.Add(new SqlParameter("@SenderId", senderId));
cmd.ExecuteNonQuery();//.ExecuteNonQuery();
int RowsAffected = Convert.ToInt32(cmd.Parameters["@RowCount"].Value);
}
Getting the return value from stored procedure and using it in program
The SQL is more complicated than it needs to be - you could use
CREATE Procedure Check_Previous_Passwords
@ua_pk uniqueidentifier,
@IncomingPassword varchar(25)
AS
SELECT COUNT(*)
FROM User_Passwords up
WHERE up.ua_fk = @ua_pk
AND @IncomingPassword = up.up_Password
and the VB which uses it needs to be a function so that it can return a value:
Option Strict On
' ...'
Public Function Check_Previous_Passwords(ByVal User As FoundationLibrary.User) As Boolean
Dim isSamePassword As Integer
Dim objCommand As New SqlCommand("Check_Previous_Passwords", DatabaseInterface_.Connection)
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Parameters.Add(New SqlParameter With {.ParameterName"@ua_pk", .SqlDbType = SqlDbType.UniqueIdentifier, .Value = ua_pk_})
objCommand.Parameters.Add(New SqlParameter With {.ParameterName = "@IncomingPassword", .SqlDbType = SqlDbType.VarChar, .Size = 25, .Value = ua_Password_})
DatabaseInterface_.Open()
isSamePassword = CInt(objCommand.ExecuteScalar)
DatabaseInterface_.Close()
User.ua_ResetPassword_ = (isSamePassword = 1)
Return User.ua_ResetPassword_
End Function
I changed the AddWithValue
parts to a version which works reliably. You should avoid AddWithValue
- it will only bring you misery eventually: Can we stop using AddWithValue() already?
Get return value from stored procedure
I found it!
I can read the return value with an output parameter that has to be used in this way:
// define a new output parameter
var returnCode = new SqlParameter();
returnCode.ParameterName = "@ReturnCode";
returnCode.SqlDbType = SqlDbType.Int;
returnCode.Direction = ParameterDirection.Output;
// assign the return code to the new output parameter and pass it to the sp
var data = _context.Database.SqlQuery<Item>("exec @ReturnCode = spItemData @Code, @StatusLog OUT", returnCode, code, outParam);
Is return value from a stored procedure must be declared as out parameter?
You can construct and call different types of stored procedures:
1) A stored procedure that returns no result. For example, such a stored procedure can log non-critical information, or change database data in a straightforward way.
Example : A stored procedure which performs insert operation.
2) A stored procedure that returns one or more values using output parameters. For example, such a procedure can indicate success or failure, or retrieve and return data items.
Example : A stored procedure which performs "Select particular_field
FROM table ..." query.
3) A stored procedure that returns one or more result sets. The procedure can execute one or more queries, each of which returns an arbitrary number of rows. Your application loops through each result set to display, transform, or otherwise process each row in it.
Example : A stored procedure which performs multiple select queries.
Hope it might help.
Related Topics
Creating Wizards For Windows Forms in C#
Dpi Awareness - Unaware in One Release, System Aware in the Other
How to Increase the Max Upload File Size in Asp.Net
Difference Between Shadowing and Overriding in C#
Converting Numbers in to Words C#
.Net Global Exception Handler in Console Application
How to Update an Observablecollection Via a Worker Thread
A Generic Error Occurred in Gdi+, Jpeg Image to Memorystream
How to Specify a Custom Location to "Search For Views" in ASP.NET MVC
How to Split CSV Whose Columns May Contain ,
Best Practices For Catching and Re-Throwing .Net Exceptions
Creating a Datetime in a Specific Time Zone in C#
How to Check For Internet Connectivity Using .Net
Why Use the 'Ref' Keyword When Passing an Object