'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 |
|---|
