checking whether a stored procedure returns anything

  • Hi all,

    This may be a simple one... but I can't get it to work... probably because my brain has gone into holiday mode without asking me! 😉

    I have a stored procedure that returns some data (like a table).

    I want to write some T-SQL that calls the stored procedure and determines whether it is empty or not.  Something like:

    select * from (exec stored_procedure_name)

    Is this possible?

    I would also like to manipulate the stored proc in other ways, such as calculating how many rows it returns, like:

    select count(*) from (exec stored_procedure_name)

    NOTE: I do not want my SQL to know anything about the inner working of the stored procedure, so for example, I dont want to create a table, fill it with the stored proc data and then do a count on the table.

    Cheers

  • Where you suppose to place this counting script?

    _____________
    Code for TallyGenerator

  • In another stored proc. 😉

    I have a stored proc that returns a table, for example, something like: sp_GetAllEmployees.

    Then I want to have a separate stored proc that determines if there are any exployees, e.g. sp_CountEmployees or something similar.

    These are for 2 different purposes.

    I would like sp_CountEmployees to somehow use sp_GetAllEmployees, so that the logic of getting the employees is not duplicated.

     

  • As far as getting the count of rows goes, the following gets it (but it also gets the entire resultset from the sp_GetAllEmployees stored proc):

    exec sp_GetAllEmployees

    select @@rowcount

     

  • I still cannot figure out - what suppose to call sp_GetAllEmployees and use its results?

    P.S. Don't use "sp_" prefix in names for SP. It's VERY bad practice.

    _____________
    Code for TallyGenerator

  • sp_CountEmployees or any other stored proc will call it.

    P.S. No worries... I was just using sp_ for clarity in this example, so it's clear that that is the stored proc.

  • So, if sp_CountEmployees will call sp_GetAllEmployees how it's gonna use returned resultset?

    _____________
    Code for TallyGenerator

  • I just want sp_CountEmployees to return the number of rows sp_GetAllEmployees returns.  That's it.

  • If you don't mind building a temporary table whose structure covers the stored procedure's results you can do something like:

    CREATE PROCEDURE dbo.usp_MyStoredProc
    AS
    SELECT 'a' union all
    SELECT 'b' union all
    SELECT 'c' union all
    SELECT 'd'

    and then

    create table #results(letter char(1))
    
    set nocount on
    insert into #results
    exec usp_MyStoredProc
    set nocount off
    
    select count(*) from #results
    
    drop table #results
  • So, you don't use resultset from sp_GetAllEmployees anywhere. Right?

    _____________
    Code for TallyGenerator

  • hi

    will using a function instead of a procedure help. performance might not be very good though.

    "Keep Trying"

  • Yep, the only way I use the resultset is to determine how many rows are returned.

    Also, a function is not an option as I will be calling the stored procs through a Linked Server. And as far as I know, it is not possible to call a function remotely through a linked server...

  • Why you need this SP if you don't use its result?

    _____________
    Code for TallyGenerator

  • Sorry, I wasn't very clear.  I use both SP's.  I just don't use the actual "data" returned by sp_GetAllEmployees.  All I am interested in is finding out how many rows sp_getAllEmployees is returning.

  • So, if you don't use actual data returned by sp_GetAllEmployees, why you need this SP at all?

    _____________
    Code for TallyGenerator

Viewing 15 posts - 1 through 15 (of 32 total)

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