Post reply

Validate whether or not a store procedure returned data?

  • December 21, 2004 at 1:48 pm

    #65864

    Is there a way to validate whether or not a stored procedure like sp_help_job returned data?

    One way would be to create a temp table, insert the results from the sp_help_job into the table and then read it but I was hoping there was an easier way.

    I want to check if "sp_help_job @job_name = 'JOBNAME', @execution_status = 4" returned data or not.

  • December 21, 2004 at 2:04 pm

    #534672

    @@rowcount


    * Noel

  • December 21, 2004 at 2:27 pm

    #534679

    @@rowcount is always 1 after executing sp_help_job no matter it the stored procedure returns data or not.

  • December 21, 2004 at 2:33 pm

    #534682

    I don't have access to my SQL Machine know but have you tried

    Select * from OpenQuery(LocalServer,'sp_help_job @job_name = 'JOBNAME', @execution_status = 4')

    if @@rowcount > 0

     Print 'Yes!'

    else

     Print 'No'

     


    * Noel

  • December 22, 2004 at 7:23 am

    #534749

    Are you using a middle tier object and ADO?  If so, you can use the RecordsAffected option.  It is important that no SP on the connection uses the SET NOCOUNT ON option. 

  • December 22, 2004 at 7:34 am

    #534750

    I am using TSQL to run the SP.

  • December 22, 2004 at 7:37 am

    #534751

    Are you by any chance running the SET NOCOUNT ON command anywhere against the connection?  It doesn't just have to be in the SP.

  • December 22, 2004 at 7:48 am

    #534754

    No, I am not issueing a SET NOCOUNT ON anywhere.

  • December 22, 2004 at 7:57 am

    #534760

    You need to provide the code for the SP and the TSQL then, because the @@rowcount is a very basic function, and I've never seen it fail when used correctly.

  • December 22, 2004 at 8:01 am

    #534762

    Here is what I have tried (the sp always returns @@rowcount of 1 whether it has data or not):

    sp_help_job @job_name = 'DLW: Test Merge Job', @job_aspect = 'JOB', @execution_status = 4

    IF @@rowcount

    BEGIN

    SELECT 'IT WORKS!!!'

    END

    ELSE

    BEGIN

    SELECT 'IT FAILS...'

    END

  • December 22, 2004 at 8:07 am

    #534764

    At first, I didn't realize you were using one of the system stored procedures.  The @@rowcount is most likely not working here because there are a lot of procedures going on in the SP.  The @@rc needs to be run immediately after the statement, and if it needs to be preserved it needs to be saved to a variable.  The moment another statement runs, that information is lost.

  • December 22, 2004 at 8:11 am

    #534766

    It should have read:

    sp_help_job @job_name = 'DLW: Test Merge Job', @job_aspect = 'JOB', @execution_status = 4

    IF @@rowcount > 1

    BEGIN

    SELECT 'IT WORKS!!!'

    END

    ELSE

    BEGIN

    SELECT 'IT FAILS...'

    END

  • December 22, 2004 at 2:39 pm

    #534834

    You can always use the return value to see if the procedure suceeded or failed it will be: 0 (success) or 1 (failure)

    DECLARE @rv int

    exec @rv = sp_help_job @job_name = 'DLW: Test Merge Job', @job_aspect = 'JOB', @execution_status = 4

    IF @rv = 0

    BEGIN

    SELECT 'IT WORKS!!!'

    END

    ELSE

    BEGIN

    SELECT 'IT FAILS...'

    END

  • December 23, 2004 at 1:53 am

    #534863

    Checking the return value doesn't say if any data was returned or not, it only tells if sp_help_job failed (which of course means there was no data )

    It seems like @@rowcount is always 1, even though many rows data is returned, so I believe the only safe bet would be to insert into a temptable and then check if you have anything in there. Doesn't look like it can be done any other way.

     

    /Kenneth

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply