'T-SQL How to pass all paramters from a stored procedure to another without knowing details

We have a number of standard stored procedures in our software that we sometimes get to customize some for customers.

CREATE PROCEDURE PROCEDURE_NAME
    @param1 int, @param2 int
AS
BEGIN
    DECLARE @spName NVARCHAR(255)
    SET @spName = OBJECT_NAME(@@PROCID)
    SET @spName = 'dbo.' + @spName + '_Custom'

    IF OBJECT_ID (@spname , N'P') IS NOT NULL
    BEGIN
        EXEC sp_executesql @spName, N'@param1 int, @param2 int', @param1, @param2
        RETURN
    END
    ...
END

This works fine, but I have to rewrite in every stored procedure to adapt the parameters list.

I'm trying to create a mechanism to call the custom version in case it exists, passing all the parameters.

I'd like to be able to use the same code for all procedures.

DECLARE @spParamtersList TABLE ([name] nvarchar(100))

INSERT INTO @spParamtersList
    SELECT [name] 
    FROM sys.parameters 
    WHERE OBJECT_ID = OBJECT_ID(@spName)

DECLARE @strParameters varchar(MAX) = SELECT @strParameters = COALESCE(@strParameters + ',' + [name], [name]) FROM @spParamtersList

This works fine, but now i have to call the custom stored procedures, I tried:

set @strSQL = N'EXEC ' + @SpName + ' ' + @strParameters
EXEC sp_executesql @strSQL

The string is correct:

'EXEC PROCEDURE_NAME_Custom @param1, @param2'

But it doesn't work.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source