Tshumore Ответов: 3

Как восстановить и связать хранимую процедуру с выходным параметром, переменной в C# код

Я пытаюсь получить самое высокое число int в смешанном столбце var/int с помощью выходного параметра хранимой процедуры таким образом, что для значений AG000123,AG000768, AG000769 я хочу получить 769. Когда я вызываю этот SP в коде C# , я получаю недопустимое исключение cast на int. Это моя хранимая процедура : 
<pre lang="SQL">CREATE PROCEDURE [dbo].[GetMaxAGCode]
	
	@returnValue INT OUT 
AS
BEGIN
	
	SET NOCOUNT ON;

SELECT MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS INT)) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
END
GO


И вот как я называю то же самое в C#:
<pre lang="c#"> using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
                {
 conn.Open();

                    SqlCommand cmd = new SqlCommand("spGetHighestAGCode", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();
                    int counter = (int)cmd.Parameters["@returnValue"].Value;
                    conn.Close();
 }


Я могу получить значение в SSMS, но в моем фактическом результирующем наборе оно возвращает null, то есть самый высокий индекс столбца имеет NULL. 
DECLARE @number INT

EXEC spGetHighestAGCode

@returnValue=@number

SELECT @number AS 'Highest index'

Верхний результирующий набор в SSMS, однако, возвращает значение, я хотел бы прикрепить скриншот для ясности, но я не вижу здесь опции.

Что я уже пробовал:

Когда я пытаюсь получить значение в виде varchar, я получаю исключение DBNull при отладке в C#. Я действительно подозреваю, что это происходит потому, что строка SELECT @number как "самый высокий индекс" в SSMS возвращает NULL.Это означает, что, хотя я могу получить правильное выходное значение, я не могу назначить его переменной @number. Я сделал это как :
ALTER PROCEDURE [dbo].[spGetHighestAGCode]
	
	@returnValue VARCHAR(50) OUT 

AS
BEGIN
	
	SET NOCOUNT ON;

    
	SELECT MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS VARCHAR(50))) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
	
END


и :
cmd.Parameters.Add("@returnValue", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
                   cmd.ExecuteNonQuery();
                   string counter = (string)cmd.Parameters["@returnValue"].Value;


Что я делаю не так?

3 Ответов

Рейтинг:
22

Maciej Los

На основе документации MSDN (Возврат данных из хранимой процедуры - SQL Server | Microsoft Docs[^])...

Если вы хотите вернуть значение из хранимой процедуры, вы должны установить значение в выходной параметр:

ALTER PROCEDURE [dbo].[spGetHighestAGCode]
	
	@returnValue VARCHAR(50) OUT 

AS
BEGIN
	
	SET NOCOUNT ON;
  
	SELECT @returnValue = MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS VARCHAR(50))) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
	
END


Затем вы сможете получить значение, возвращаемое SP.

Tshumore

Я изменил свой SP, как указано выше. В коде я больше не получаю исключение DBNull, я могу получить значение, но оно ретерирует 0.

MadMyche

Какое значение вы получите, если запустите этот запрос в SSMS?

Maciej Los

Итак, вы должны проверить, почему это происходит.

MadMyche

+5

Maciej Los

Спасибо.

Рейтинг:
1

OriginalGriff

Проблема, вероятно, заключается в ваших данных: запустите этот запрос SELECT непосредственно в SSMS без MAX и посмотрите, какие результаты вы получите - он также возвращает Linked_sun_Id, должно быть проще отследить, какие данные являются недействительными.

Скорее всего, хотя бы одно из значений, которые вы пытаетесь преобразовать в целое число, не является конвертируемым, подстрока не работает для всех значений идентификатора. Это отчасти подтверждается результатом, когда вы пытаетесь вернуть VARCHAR и получить нулевые значения.

Tshumore

Выполняя запрос как есть без Макса, я получаю только целочисленные части, то есть 123, 768, 769. Я изменил код на
int counter = конвертировать.ToInt32(cmd.Parameters["@returnValue"].Value); и действительно, я все еще получаю объект, который не может быть приведен из dbnull в другие типы

Рейтинг:
0

MadMyche

Проблема, которую вы первоначально опубликовали, заключается в том, что ваша хранимая процедура будет возвращать результирующий набор, а не заполненное значение; в то время как вы объявляете значение, вы никогда его не устанавливаете. Это средство предусмотрено в ответе № 2

Теперь есть альтернатива, так как это возвращает результирующий набор, и в этом наборе есть только 1 значение, которое необходимо; вы можете просто удалить выходной параметр и использовать метод ExecuteScalar() 

ALTER PROCEDURE [dbo].[spGetHighestAGCode]
-- @returnValue VARCHAR(50) OUT
AS
BEGIN
   SET NOCOUNT ON;

   SELECT MAX(CAST(SUBSTRING(Linked_Sun_Id, 4, LEN(Linked_Sun_Id)-3) AS VARCHAR(50))) FROM BrokerMappings WHERE Linked_Sun_Id LIKE 'AG%'
END
using (SqlConnection conn = new SqlConnection(Helpers.DatabaseConnect))
{
   conn.Open();

   SqlCommand cmd = new SqlCommand("spGetHighestAGCode", conn);
   cmd.CommandType = CommandType.StoredProcedure;

   //cmd.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;
   //cmd.ExecuteNonQuery();
   //int counter = (int)cmd.Parameters["@returnValue"].Value;

   int counter = Convert.ToInt32(cmd.ExecuteScalar());
   conn.Close();
}
Одна вещь, чтобы не то, что я не использовал (int)(cmd.ExecuteScalar()) и решил использовать Convert.ToInt32() метод, который не будет восприимчив к наличию пустого возвращаемого набора. Это, однако, возвращает пустая ссылка исключение, если значение возвращается значение null