0

I am creating a stored procedure to produce a list of items. The stored procedure returns a result and a return value. In my application that I am calling the stored procedures return the return value. How do I make it return the result?

This is my procedure:

CREATE PROCEDURE [AQB_RMS].[p_SO2EmailOverDue]
AS
    (SELECT
         CONVERT(CHAR(10), CheckDate, 101) AS ZSPDate, 
         Manufacturer, Model, SerialNumber, LocationName
     FROM
         [AQB_RMS].[SO2_Zsp] so
     INNER JOIN
         AQB_MON.[AQB_RMS].[Device] dev ON dev.DeviceID = so.DeviceID
     INNER JOIN
         AQB_MON.[AQB_RMS].[DeviceLocation] dl ON dev.DeviceID = dl.DeviceID
     INNER JOIN
         AQB_MON.[AQB_RMS].[Location] loc ON dl.LocationID = loc.LocationID
     INNER JOIN
         [AQB_RMS].[ManufacturerModel] mm ON dev.ManufacturerModelID = mm.ManufacturerModelID
     INNER JOIN
         [AQB_RMS].[Manufacturer] man ON mm.ManufacturerID = man.ManufacturerID
     WHERE
         CheckDate = (SELECT MAX(CheckDate) FROM [AQB_RMS].[SO2_Zsp]
                      WHERE DeviceID = so.DeviceID) 
         AND dl.EndDate IS NULL 
         AND (SELECT DATEDIFF(day, so.CheckDate, GetDate()) AS DayCount) > 14)
     ORDER BY
         CheckDate

    RETURN 
GO

The following is the return when I execute the stored procedure in SQL Server 2012

enter image description here

I am using the stored procedure to provide the results for the body of an email. I am using c#

    static string Body()
    {
        //create a connection to the database
        string ConnString = ConfigurationManager.ConnectionStrings["avdatauser"].ConnectionString;

        StringBuilder sb = new StringBuilder();

        using (SqlConnection con = new SqlConnection(ConnString))
        {
            con.Open();

            SqlCommand cmd = new SqlCommand("AQB_RMS.p_SO2EmailNearDue", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();

            sb.Append("<strong>SO2 Analyzer ZSP due</strong>");
            sb.Append("<br>");
            sb.Append("<br>");
            sb.Append("What do you want to say here to show these are the ones with a ZSP that are near due");
            sb.Append("<br>");
            sb.Append("<br>");
            sb.Append(" " + cmd + " ");
            con.Close();
        }

        return sb.ToString();
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ethel Patrick
  • 885
  • 7
  • 18
  • 38
  • 1
    What language are you using for your application? What does the code look like that is calling the SP and and consuming the results? – daShier Jan 08 '20 at 15:51
  • 2
    The return statement in a procedure is used to return an integer which is intended to indicate a status of the execution. Not sure what you are trying to do here. Are you trying to get the results of the query into an object in your dotnet code? There are dozens of ways to do this and literally millions of example all over the internet. – Sean Lange Jan 08 '20 at 16:33
  • I am trying to use the stored procedure to return the results value of ZSP Date, Manufacurer, Model and LocationName. I have been looking and getting confused with all the examples – Ethel Patrick Jan 08 '20 at 16:43
  • 1
    IF you want to read the data selected by the `SELECT` statement, you **must not** call `.ExecuteNonQuery()` (that's for `DELETE, INSERT, UPDATE` statements) - but instead use `.ExecuteReader` and iterate over the rows returned by the reader – marc_s Jan 08 '20 at 16:50
  • 1
    @SeanLange I think millions of examples is a low estimate here. This is a good starting spot, though: https://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program – Jacob H Jan 08 '20 at 17:02
  • Thank you for all the suggestions. I resolved my issue by using a TableAdapter in my application – Ethel Patrick Jan 08 '20 at 21:29

0 Answers0