I have a stored procedure that looks something like this,

CREATE PROCEDURE my_schema.sp_do   
    @param1 VARCHAR(50),   
    @param2 VARCHAR(MAX),
    @count INT OUTPUT
AS   
    -- execute logic
    SET @count = (SELECT col1 FROM @tbl)
GO

This will basically execute some logic and set the @count output variable to some value, which I want to be able to access when the stored procedure is actually called.

I am able to execute it like this and get the value of the output,

DECLARE @i INT
EXEC my_schema.sp_do 'val1', 'val2', @i OUTPUT;
PRINT @i

However, when I am actually making use of this, I am building this query dynamically. How exactly can I execute this query as a string and still be able to access the output?

For instance,

DECLARE @i INT
SET @sql = 'EXEC my_schema.sp_do ''val1'', ''val2'', @i OUTPUT;'
exec sp_executesql @sql
PRINT @i

I know this does not work. How can I do this correctly?

If I understand correctly, I need to pass names and the data types of the parameters as the second argument to sp_executesql ? Since I am building the query dynamically, I cannot do this. I don't have access to the data types, I only have access to the parameter names and the values.

If there is an entirely better way to do this, I am open to that as well.

Note: As to why I need to do this, I have several stored procedures and each of them have their own parameters. These stored procedures are defined by a number of database admins. This essentially means that the numbers of SPs will increase/decrease over time (however, the structure will remain somewhat the same: the count OUTPUT needs to be available in every SP). There are users who configure these stored procedures to be executed by providing the parameters and a kind of alias to the stored procedure itself (this information is stored in table, let's call it the metadata table). This is done through an application. I am in the process of writing a controller of sorts that will query the metadata table and execute each of these configured stored procedures on a schedule.

Using the metadata table, I can build the path to each stored procedure and I can pass in the parameters. However, I am not entirely sure how the data types of the parameters can be accessed. Is there a way to do this?