Return value from one stored procedure to another

Question

I want to use the return value from one stored procedure to another stored procedure. I was searching on the internet and try several solutions, but all of them are not working, I don't know what the mistake I make.

The stored procedure that I want to use its return value is:

CREATE PROCEDURE dbo.pro_ForeignKeyCheck
     (@tableName VARCHAR(100),
      @columnName VARCHAR(100),
      @idValue INT) 
AS BEGIN
    SET NOCOUNT ON

    DECLARE fksCursor CURSOR FAST_FORWARD FOR 
        SELECT 
            tc.table_name, ccu.column_name
        FROM
            information_schema.table_constraints tc 
        JOIN
            information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name 
        JOIN
            information_schema.referential_constraints rc ON tc.constraint_name = rc.constraint_name 
        JOIN
            information_schema.table_constraints tc2 ON rc.unique_constraint_name = tc2.constraint_name 
        JOIN
            information_schema.constraint_column_usage ccu2 ON tc2.constraint_name = ccu2.constraint_name 
        WHERE
            tc.constraint_type = 'Foreign Key' 
            AND tc2.table_name = @tableName 
            AND ccu2.column_name = @columnName
        ORDER BY
            tc.table_name

    DECLARE @fkTableName VARCHAR(100),
            @fkColumnName VARCHAR(100),
            @fkFound BIT,
            @params NVARCHAR(100),
            @sql NVARCHAR(500)

    OPEN fksCursor

    FETCH NEXT FROM fksCursor INTO @fkTableName, @fkColumnName

    SET @fkFound = 0
    SET @params = N'@fkFound BIT OUTPUT'

    WHILE @@fetch_status = 0 AND COALESCE(@fkFound, 0) <> 1 
    BEGIN
        SELECT @sql = 'set @fkFound = (select top 1 1 from [' + @fkTableName + '] where [' + @fkColumnName + '] = ' + cast(@idValue as varchar(10)) + ')'
        PRINT @sql

        EXEC sp_executesql @sql, @params, @fkFound OUTPUT

        FETCH NEXT FROM fksCursor INTO @fkTableName, @fkColumnName
    END

    CLOSE fksCursor
    DEALLOCATE fksCursor

    SELECT COALESCE(@fkFound, 0)
    RETURN 0
END

and this use to check if the primary key value used in all child tables, we call it like this

EXECUTE  pro_ForeignKeyCheck 'tablename','columnName', 1

or

EXECUTE  pro_ForeignKeyCheck @tablename = 'tablename', @columnName = 'columnName', @idValue = 1

and it will work, but I cannot use the return value in other stored procedure

CREATE  PROCEDURE [dbo].[pro_Delete_acount]
    @UserID int,
    @Action NVARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE  @count int , @ErrNo int , @ErrMsg varchar(2000), @exit int



    set @exit=0
    if @Action ='ADMIN'
    begin
    /*---------Call Store Procedure pro_ForeignKeyCheck to check if there is value are exit in child table*/
    --EXEC  @exit = pro_ForeignKeyCheck1 @tablename='tb_M_admin',@columnName='admin_id',@idValue= @UserID 
    --select @exit

    --EXEC   @exit = pro_ForeignKeyCheck  @tablename='tb_M_admin',@columnName='admin_id',@idValue= 1

    EXEC @exit = pro_ForeignKeyCheck 'tb_M_admin','admin_id', 0 
    --select @exit

    select -2[Ret_Status],@exit[ErrNo],0[ErrMsg] -- test 

    end
end

Could anyone help me with that?

Thanks all

Answer 1

Add

@Status int OUTPUT

in the pro_ForeignKeyCheck so it starts with

CREATE PROCEDURE dbo.pro_ForeignKeyCheck1
@tableName VARCHAR(100),
@columnName VARCHAR(100),
@idValue int,
@Status int OUTPUT

and at the end of it did as follow

--select coalesce(@fkFound,0)
select @Status = coalesce(@fkFound,0)
--return 0

stop the last to line and add new one

In the other stored procedure, call it as follows

EXEC  pro_ForeignKeyCheck1 'tb_M_admin','admin_id', 0 ,@exit output 
select @exit

and now the return value will be used.

Thanks to all

Answer 2

This is oversimplified example of call of one SP from another. I hope it will give you some ideas.

create procedure dbo.first_proc
@bd datetime,
@d int output
as
select @d= DATEDIFF(day,@bd,getdate()) 
go

create procedure dbo.sec_proc
@birthday datetime
as
declare @days int
exec dbo.first_proc @birthday, @days output

select 'you live '+cast(@days as varchar) + ' days' result
go

exec dbo.sec_proc '1959-09-17'

Answer 3

EXEC @exit = pro_ForeignKeyCheck 'tb_M_admin','admin_id', 0 

---
select coalesce(@fkFound,0)
return 0 --< this will be assigned to @exit

replace this code with

return IsNull(@fkFound, 0)

You may leave select for other purposes but it cannot affect RETURN value. So you may remove it either.