Databases
StackOverflow2013
Postsdbo
POC# and SQL Server: Get result from stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POC# and SQL Server: Get result from stored procedure
    primarykey
    data
    text
    <p>I have the following stored procedure that calculates the maximum values for multiple columns. I have achieved the same result using a lengthy union select, however, I am just interested in the performance difference. Using a C# direction SQL parameter of either output or a return value, I would like to only return the value of the select statement marked "-- NEED TO GET THIS VALUE RETURNED".</p> <p>I tried creating an OUTPUT variable, then using the SET syntax to store the value, then the RETURN syntax. But to no avail. Kept of getting errors that I need to create variables that are already there. I am not understanding the scope of the variables it seems. (I have removed that from the code in the meantime)</p> <p>Here is my SP:</p> <pre><code>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; </code></pre> <p>Herewith my revised code:</p> <pre><code>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 </code></pre> <p>c# example of executing the SP:</p> <pre><code>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) { } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThomas
    1. USThomas
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POC# and SQL Server: Get result from stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPlease note that I do know how to execute the stored procedure and send it variables. Also how to get the results. BUT, I don't know what to change in the SP that only the single result is returned from the SELECT MAX(Result) AS MaxVar from @Result query
      singulars
      1. POC# and SQL Server: Get result from stored procedure
      1. USThomas
    2. COIn what sense is it not returning a single result now?
      singulars
      1. POC# and SQL Server: Get result from stored procedure
      1. USAndomar
    3. CO@Andomar: I almost had it correct. Just needed the RETURN clause. It does return a single value. But not implicitly.
      singulars
      1. POC# and SQL Server: Get result from stored procedure
      1. USThomas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload