Stored procedure execute another sp with random value of parameters [closed]

For example:

exec SpFirst
    @param1 = 1,
    @param2 = 'sdad',
    @randomSP = 'nameOfRandomSP @n1 = 1 @n2 = "2"'

In SpFirst i use “exec @randomSP” but don’t know how send to SpFirst send parameters @n1 and @n2 and maybe more. Becouse i use SpFirst to execute 10 another sp with different value of parameters

Try not to reinvent the wheel when you don’t need to. Take a look at sp_executesql and all that it has to offer you:

https://msdn.microsoft.com/en-us/library/ms188001.aspx

You can pass in parameters to it like so:

USE AdventureWorks2012;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID';

SET @ParmDefinition = N'@CustomerID int
  , @SalesOrderOUT nvarchar(25) OUTPUT';

SET @IntVariable = 22276;

EXECUTE sp_executesql @SQLString
      , @ParmDefinition
      , @CustomerID = @IntVariable
      , @SalesOrderOUT = @SalesOrderNumber OUTPUT;

If this doesn’t fit your needs then you could always use dynamic sql to generate your procedure calls. However, you would still need to know how many parameters to pass to the procedure call. You cannot pass an unlimited amount.

There are ways around this however using XML and Table-Valued-Parameters. You don’t specify the version of SQL Server you are using and so I am going to assume a minimum of 2008. As such, there are a few ways you can pass a variable amount of parameters to a ‘host’ procedure which then generates the SQL command for you. This is my preferred method:

CREATE TYPE VariableParameters AS TABLE (
    ParameterID INT IDENTITY NOT NULL,
    ParameterName NVARCHAR(128) NOT NULL,
    ParameterValue NVARCHAR(MAX) NULL
);

CREATE PROCEDURE dbo.Dispatcher
    @TargetProcedureName NVARCHAR(128),
    @ParameterList VariableParameters READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Parameters NVARCHAR(MAX);
DECLARE @ID INT = (SELECT MIN(ParameterID) FROM @ParameterList);
DECLARE @Max INT = (SELECT MAX(ParameterID) FROM @ParameterList);
WHILE @ID <= @Max
BEGIN
IF @ID < @Max AND @Parameters IS NOT NULL SET @Parameters = @Parameters + ', ';
SET @Parameters = (
    SELECT @Parameters + ParameterName + ' = ' + ParameterValue 
      FROM @ParameterList WHERE ParameterID = @ID);
SET @ID = @ID + 1;
END
DECLARE @Command NVARCHAR(MAX) = 'EXEC ' + @TargetProcedureName + ' ' + @Parameters;
EXECUTE (@Command);
END

Then you would use it like so:

DECLARE @Parameters VariableParameters;
INSERT @Parameters (ParameterName, ParameterValue)
VALUES ('@p1', '1');

EXEC dbo.Dispatcher @TargetProcedureName = 'MyProc', @ParameterList = @Parameters;

You can find the documentation for CREATE TYPE here:

https://msdn.microsoft.com/en-us/library/ms175007.aspx

And documentation on how to use table types here:

https://msdn.microsoft.com/en-us/library/bb510489.aspx

Now this is completely untested and off the top of my head but you should be able to get the gist of what is going on. The host procedure iterates through the parameters to build the SQL to be executed. It is up to you to add validation and sanitation routines etc to prevent a SQL injection attack.

I hope this helps you.

Technically you could do it if SpFirst was set up to construct the sp call using dynamic SQL but I can’t imagine any reason why you would want to.