none
How do I return a TRUE or FALSE using a Stored Procedure?

    Pregunta

  • How do I return a TRUE or FALSE using a Stored Procedure?

    This is my SQL Server Code:
    All I need it to do is return a TRUE or FALSE response (in integer, string, I don't care what manner) as to whether or not the given select statement returned with data (in other words, if something met the criteria).

    The criteria is "Do any records nameKey = 52 and were they posted on today's date?"

    ---------------------------------------
    CREATE PROCEDURE udspCheckForMilestoneUpdates
    AS

    IF EXISTS
        (
        SELECT *
        FROM storyData
        WHERE nameKey = 52
            AND
            date_posted = CONVERT(varchar, DATEADD([year], 0, GETDATE()), 101)
        )
        BEGIN
            RETURN 1
        END
    ELSE
        BEGIN
            RETURN 0
        END
    GO
    ---------------------------------------

    In SQL Query Analyzer, when I "EXEC udspCheckForMilestoneUpdates" it says:
    "The command(s) completed successfully."

    However, the Grid tab shows there is no data, and my C# code's DataSet has no data put into it...

    Here the C# for those interested:
    ---------------------------------------
    DataSet dsAnswer;
    string StringConnection;
    StringConnection = ConfigurationSettings.AppSettings["ConnectionStringStoryDB"];

    dsAnswer = SqlHelper.ExecuteDataset(StringConnection,CommandType.StoredProcedure,"storedprocCheckForMilestoneUpdates");

    int intCatcher = Convert.ToInt32(dsAnswer.Tables[0].Rows[0][0].ToString());

    if (intCatcher)...
    ---------------------------------------

    Any help is greatly appreciated.

     

     

     

    viernes, 14 de octubre de 2005 16:36

Respuestas

  • Please do not use the SELECT approach to return scalar values from SPs. It is inefficient. The return value will work fine. I don't know what your helper class does but if you define a SqlParameter object with ParameterDirection as ReturnValue then you can get the return value. This is the preferred way to do this from the client. Keep SELECT statement to return multiple value / rows else output/return value should suffice.
    viernes, 14 de octubre de 2005 21:53

Todas las respuestas

  • Try something like this using an output parameter

    CREATE PROCEDURE jerrytest2
    (
       @iReturnedStuff bit output
    )
    AS
    Declare @mTot int
    Begin
     SELECT @mTot = Count(*)
     FROM myTable
    END

    Begin
    if @mTot > 0
     set @iReturnedStuff = 1
    else
     set @iReturnedStuff = 0
    End
    GO

     

    viernes, 14 de octubre de 2005 18:21
  • It turns out that replacing RETURN 1 and RETURN 0 with SELECT 1 and SELECT 0, respectively, makes the whole thing work.

    Thank you for your time, Jerry!
    viernes, 14 de octubre de 2005 18:24
  • Please do not use the SELECT approach to return scalar values from SPs. It is inefficient. The return value will work fine. I don't know what your helper class does but if you define a SqlParameter object with ParameterDirection as ReturnValue then you can get the return value. This is the preferred way to do this from the client. Keep SELECT statement to return multiple value / rows else output/return value should suffice.
    viernes, 14 de octubre de 2005 21:53
  • can anyone post a sample code to get the return value using ado.net.

    thanks

    martes, 14 de febrero de 2006 23:56
  • pseudocode:

    • create an SqlCommand object
    • set command text to the stored procedure name
    • set the command type to stored procedure
    • call SqlCommand.ExecuteScalar() method
      • (The value that comes out of ExecuteScalar is your return value.)
    miércoles, 15 de febrero de 2006 15:59
  • Got it! thanks Jeremy R.
    miércoles, 15 de febrero de 2006 17:06