Insert result into temp table

Question

So I've read these posts

• Using a Variable in OPENROWSET Query
• Insert results of a stored procedure into a temporary table

And with that I came up with this solution

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

alter procedure [CP24SHOP].[spSearch_DitoNr_OemNr_EngineType]
    @user           nvarchar(255),
    @ditoNr         int,
    @oemNr          nvarchar(255) = null,
    @engineType     nvarchar(255) = null
as
begin
    if(@oemNr is null and @engineType is null)
        exec CP24SHOP.spSearchDitoNr
            @user = @user,
            @ditoNr = @ditoNr
    else
        begin
            declare @sql nvarchar(max) = 'SELECT *  
                                        FROM OPENROWSET(
                                            ''SQLNCLI'', 
                                            ''Server=localhost\ISTABGLOBAL;Trusted_Connection=yes;'',
                                            ''exec CP24SHOP.spSearchDitoNr
                                                @user = ' + @user + ',
                                                @ditoNr = ' + @ditoNr + ''')'

            --exec (@sql)

            SELECT * INTO #TempTable FROM (@sql) -- Line 33
                where OEMNumber = @oemNr
        end
end
return 0;

however upon running this scripts I get the following error:

Msg 102, Level 15, State 1, Procedure spSearch_DitoNr_OemNr_EngineType, Line 33
Incorrect syntax near ')'.

Any idea of whats wrong with my syntax cause for me this is new territory

EDIT What I'm trying to do is to call a stored procedure CP24SHOP.spSearchDitoNr and then only select those rows where OEMNumber = @oemNr

Answer 1

You can't use SELECT * INTO #TempTable FROM (@sql) because @sql is a nvarchar(max). You need to save the values from your OPENROWSET query into a table.

For more information have a look at this post. You can use INSERT INTO myTable EXEC sp_executesql(@sql).