Блог

У меня есть следующая хранимая процедура, которая вычисляет максимальные значения для нескольких столбцов. Я добился того же результата, используя длинный выбор объединения, однако меня просто интересует разница в производительности. Используя параметр C # direction SQL либо для вывода, либо для возвращаемого значения, я хотел бы возвращать только значение инструкции select с пометкой «— НЕОБХОДИМО ВЕРНУТЬ ЭТО ЗНАЧЕНИЕ».

Я попытался создать ВЫХОДНУЮ переменную, затем использовать синтаксис SET для сохранения значения, затем синтаксис RETURN. Но безрезультатно. Избегал получать ошибки, которые мне нужны для создания переменных, которые уже есть. Кажется, я не понимаю область действия переменных. (Тем временем я удалил это из кода)

Вот мой SP:

 CREATE PROCEDURE sp_GetMaximumMax
  @FromDate DateTime,
  @ToDate DateTime,
  @TableName VarChar(150)
AS

DECLARE @SQL NVARCHAR(512), @ColumnName VARCHAR(150)
DECLARE @Result TABLE (Result int)

DECLARE read_cursor CURSOR FOR
   SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS  
   WHERE TABLE_NAME = @TableName 
     AND (column_name != 'id' AND column_name !='time' AND column_name !='no')

OPEN read_cursor;

FETCH NEXT FROM read_cursor INTO @ColumnName;

SET @SQL = 'SELECT MAX([' @ColumnName ']) FROM ' @TableName ' WHERE [time] BETWEEN ''' CONVERT(VARCHAR, @FromDate, 120) ''' AND ''' CONVERT(VARCHAR, @ToDate, 120) '''';

WHILE @@FETCH_STATUS = 0
BEGIN
Print @SQL
INSERT  @Result 
EXEC(@SQL);
FETCH NEXT FROM read_cursor
END

-- NEED TO GET THIS VALUE RETURNED
SELECT MAX(Result) AS MaxVar from @Result

CLOSE read_cursor;
DEALLOCATE read_cursor;
  

При этом мой пересмотренный код:

 CREATE PROCEDURE sp_GetMaximumAvg

@FromDate DateTime,
@ToDate DateTime,
@TableName VarChar(150)

AS
CREATE TABLE #Fields(FieldName VARCHAR(150)) -- store column names
CREATE TABLE #Data(FieldName DECIMAL) -- store max values
DECLARE @ColumnName VARCHAR(150)
DECLARE @SQL VARCHAR(512)

-- add fields into field table
INSERT INTO #Fields (FieldName) SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS      WHERE TABLE_NAME = @TableName AND (column_name != 'id' AND column_name !='time' AND column_name !='no')

DECLARE @Field VARCHAR(150)
DECLARE @MyOutput INT
DECLARE ReadDaRow CURSOR

FOR SELECT * FROM #Fields

OPEN ReadDaRow
FETCH ReadDaRow INTO @Field
WHILE(@@FETCH_STATUS=0) BEGIN
SET @SQL = 'SELECT AVG([' @Field ']) FROM ' @TableName ' WHERE [time] BETWEEN ''' CONVERT(VARCHAR, @FromDate, 120) ''' AND ''' CONVERT(VARCHAR, @ToDate, 120) '''';
PRINT @SQL
INSERT INTO #Data EXEC(@SQL)
FETCH ReadDaRow INTO @Field
END

SELECT @MyOutput = MAX(FieldName) FROM #Data

RETURN @MyOutput

CLOSE ReadDaRow
DEALLOCATE ReadDaRow

DROP TABLE #Fields
DROP TABLE #Data
  

c # пример выполнения SP:

 SqlCommand GetMaxCmd = new SqlCommand("sp_GetMaximumAvg", SQLConnection);
GetMaxCmd.CommandType = System.Data.CommandType.StoredProcedure;
GetMaxCmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value =     DateTime.Parse(from_date);
GetMaxCmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = DateTime.Parse(to_date);
GetMaxCmd.Parameters.Add("@TableName", SqlDbType.VarChar).Value = table_name;

SqlParameter Output = GetMaxCmd.Parameters.Add("@MyOutput", SqlDbType.SmallInt);
Output.Direction = ParameterDirection.ReturnValue;

try
{
    GetMaxCmd.ExecuteNonQuery();
    Max = Convert.ToInt32(Output.Value);
}
catch (NullReferenceException) { }
catch (FormatException) { }
catch (SqlException) { }
  

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

РЕДАКТИРОВАТЬ: Вы можете return использовать его вместо набора строк:

 CLOSE read_cursor;
DEALLOCATE read_cursor;

RETURN (SELECT MAX(Result) AS MaxVar from @Result)
  

В конечном итоге это будет в параметре с направлением System.Data.ParameterDirection.ReturnValue .

Или вы могли бы прочитать это с помощью SqlCommand.ExecuteScalar() .