how to throw error from my procedure so that sp_execute_remmote catches the faliure and returns npn 0 value

To throw an error from your stored procedure so that sp_execute_remote catches it and returns a non-zero value, you need to use the THROW or RAISEERROR statements within the stored procedure. These statements generate errors that propagate to the calling sp_execute_remote, which will handle the failure.

Example of using THROW:

CREATE PROCEDURE [dbo].[YourProcedureName]

AS

BEGIN

    BEGIN TRY

        -- Your procedure logic goes here

        -- Simulate an error if something goes wrong

        THROW 50000, 'An error occurred in the procedure.', 1;

    END TRY

    BEGIN CATCH

        -- Handle the error or log it, if needed, before re-throwing

        THROW;

    END CATCH

END

Example of using RAISEERROR:

CREATE PROCEDURE [dbo].[YourProcedureName]

AS

BEGIN

    BEGIN TRY

        -- Your procedure logic goes here

        -- Simulate an error

        RAISERROR('An error occurred in the procedure.', 16, 1);

    END TRY

    BEGIN CATCH

        -- Optionally handle the error or log it, then re-throw

        RAISERROR('Re-throwing error from CATCH block.', 16, 1);

    END CATCH

END

Using sp_execute_remote:

Once your procedure throws an error, when sp_execute_remote runs this procedure, it will catch the error and return a non-zero value. Here's an example of how to use sp_execute_remote to call this procedure remotely:

EXEC sp_execute_remote

    @data_source = N'YourExternalDataSource',

    @stmt = N'EXEC YourDatabase.dbo.YourProcedureName;';

If an error is thrown from the remote procedure, the error code returned by sp_execute_remote will be non-zero, indicating failure.