OPENROWSET on sp_executesql with two dynamic parameters

Question

Can someone give me a working example of using OPENROWSET on SQL Server with dynamic SQL (sp_executesql) executing a stored procedure with two parameters as variables, preferably date and integer? I want to use that output and INSERT INTO a temp table

INSERT INTO #t
SELECT * FROM OPENROWSET
(
    SQL Server
    SP with two parameters date and integer)
   
SELECT * FROM #t

Answer 1

This is a job for dynamic SQL. You can find extensive coverage here and here.

DECLARE @sql    nvarchar(MAX),  
        @params nvarchar(4000)

SELECT @params = N'@YourDate    date, 
                   @YourInt     int'

SELECT @sql = 
    'INSERT INTO #t
        SELECT *
        FROM OPENROWSET(
            ''Provided Name, e.g. SQLNCLI'',
            ''Provided String e.g. Server=Seattle1;Trusted_Connection=yes;'',
            ''EXEC YourProcedure @YourDate, @YourInt'')'

PRINT @SQL
EXEC sp_executesql @sql, @params, @YourDate = '20220101', @YourInt = 12345

SELECT * FROM #t

This dbfiddle shows that the PRINT's output looks correct, but I must admit that I don't have the right setup on my end to actually test the EXEC.