[Sql] Как получить возвращаемое значение хранимой процедуры


Answers

Кроме того, чтобы получить результат (или любой другой выходной параметр, если на то пошло) из ADO.NET, вы должны сначала пропустить все возвращаемые результирующие наборы (или пропустить их с помощью NextResult)

Это означает, что если у вас есть такая процедура, как:

CREATE PROC Test(@x INT OUT) AS
    SELECT * From TestTable
    SELECT @x = 1

И постарайтесь сделать это:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test"
cmd.Parameters.Add("@x", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@retval", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

cmd.Execute();
int? x = cmd.Parameters["@x"].Value is DBNull ? null : (int?)cmd.Parameters["@x"].Value;

Тогда x будет содержать null. Чтобы он работал, вам необходимо выполнить следующую процедуру:

using (var rdr = cmd.ExecuteReader()) {
    while (rdr.Read())
        MaybeDoSomething;
}
int? x = cmd.Parameters["@x"].Value is DBNull ? null : (int?)cmd.Parameters["@x"].Value;

В последнем случае x будет содержать 1, как ожидалось.

Question

Вероятно, это простой ответ. У меня есть эта процедура:

CREATE PROCEDURE [dbo].[AccountExists]
    @UserName nvarchar(16)
AS
IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName)
SELECT 1
ELSE SELECT 0 

Когда у меня есть код ADO.NET, который вызывает эту процедуру, и делает следующее:

return Convert.ToBoolean(sproc.ExecuteScalar());

Возвращается либо true, либо false.

Когда я меняю хранимую процедуру на RETURN 1 или 0 вместо SELECT:

ALTER PROCEDURE [dbo].[AccountExists]
    @UserName nvarchar(16)
AS
IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName)
RETURN 1
ELSE RETURN 0 

sproc.ExecuteScalar () возвращает значение null. Если я попробую sproc.ExecuteNonQuery (), то возвращается -1.

Как получить результат хранимой процедуры с помощью RETURN в ADO.NET?

Мне нужны AccountExists для RETURN вместо SELECT, поэтому я могу использовать другую хранимую процедуру:

--another procedure to insert or update account

DECLARE @exists bit

EXEC @exists = [dbo].[AccountExists] @UserName 

IF @exists=1
--update account
ELSE
 --insert acocunt



Я пробовал другие решения с моей настройкой, и они не работали, но я использую VB6 & ADO 6.x. Я также хочу отметить, что результат возврата 0 указывает на успешность. Не забывайте, что есть также функции, которые не имеют этого соглашения. Нашел это на MSDN, и это сработало для меня:

Debug.Print "starting at ..." & TimeValue(Now)

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
'These are two possible connection strings. You could also have Integrated Security instead of these for SqS for security
'cn.ConnectionString = "Data Source=[yourserver];User ID=[youruser];Password=[yourpw];Initial Catalog=[yourdb];Provider=SQLNCLI10.1;Application Name=[yourapp]"
cn.ConnectionString = "Data Source=[yours];User ID=[youruser];Password=[yourpassword];Initial Catalog=[Yourdb];Provider=sqloledb;Application Name=[yourapp]"
cn.Open

cmd.ActiveConnection = cn
cmd.CommandText = "AccountExists"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("UserName",adVarChar, adParamInput, 16, UserNameInVB)

cmd.Execute
Debug.Print "Returnval: " & cmd.Parameters(0)
cn.Close

Set cmd = Nothing
Set cn = Nothing

Debug.Print "finished at ..." & TimeValue(Now)

Результаты будут отображаться в непосредственном окне при запуске (Debug.Print)




Существует несколько способов вернуть значения с помощью VBA:

  1. Recordset
  2. Количество затронутых записей (только для Insert / Update / Delete в противном случае -1)
  3. Выходной параметр
  4. Возвращаемое значение

Мой код демонстрирует все четыре. Вот хранимая процедура, которая возвращает значение:

Create PROCEDURE CheckExpedite
    @InputX  varchar(10),
    @InputY int,
    @HasExpedite int out
AS
BEGIN
    Select @HasExpedite = 9 from <Table>
    where Column2 = @InputX and Column3 = @InputY

    If @HasExpedite = 9
        Return 2
    Else
        Return 3
End

Вот что я использую в Excel VBA. Вам понадобится ссылка на Microsoft ActiveX Data Objects 2.8 Library.

Sub CheckValue()

    Dim InputX As String: InputX = "6000"
    Dim InputY As Integer: InputY = 2014

    'open connnection
    Dim ACon As New Connection
    ACon.Open ("Provider=SQLOLEDB;Data Source=<SqlServer>;" & _
        "Initial Catalog=<Table>;Integrated Security=SSPI")

    'set command
    Dim ACmd As New Command
    Set ACmd.ActiveConnection = ACon
    ACmd.CommandText = "CheckExpedite"
    ACmd.CommandType = adCmdStoredProc

    'Return value must be first parameter else you'll get error from too many parameters
    'Procedure or function "Name" has too many arguments specified.
    ACmd.Parameters.Append ACmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue)
    ACmd.Parameters.Append ACmd.CreateParameter("InputX", adVarChar, adParamInput, 10, InputX)
    ACmd.Parameters.Append ACmd.CreateParameter("InputY", adInteger, adParamInput, 6, InputY)
    ACmd.Parameters.Append ACmd.CreateParameter("HasExpedite", adInteger, adParamOutput)

    Dim RS As Recordset
    Dim RecordsAffected As Long

    'execute query that returns value
    Call ACmd.Execute(RecordsAffected:=RecordsAffected, Options:=adExecuteNoRecords)

    'execute query that returns recordset
    'Set RS = ACmd.Execute(RecordsAffected:=RecordsAffected)

    'get records affected, return value and output parameter
    Debug.Print "Records affected: " & RecordsAffected
    Debug.Print "Return value: " & ACmd.Parameters("ReturnValue")
    Debug.Print "Output param: " & ACmd.Parameters("HasExpedite")

    'use record set here
    '...

    'close
    If Not RS Is Nothing Then RS.Close
    ACon.Close

End Sub



Links