0

So I've read these posts

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

Community
  • 1
  • 1
Michael Tot Korsgaard
  • 3,892
  • 11
  • 53
  • 89
  • 1
    You should identify line 33 referred to in the error message. As writting, the last `SELECT` makes no sense at all, but the commented out `exec` suggests that you understand this. – Gordon Linoff Jul 08 '16 at 11:30
  • Looks like you're missing `'` around `+@user` at least - so I think the problem comes down to your `@sql` string not being build correct as well as what Gordon mentions with the `SELECT` – Allan S. Hansen Jul 08 '16 at 11:31
  • and if your `@user` is indeed a varchar, you miss an `' ` before and after – A ツ Jul 08 '16 at 11:32
  • I've tried changing ´@user = ' +@user + ',´ into ´@user = ''' +@user + ''',´ I still get the same error message – Michael Tot Korsgaard Jul 08 '16 at 11:38

1 Answers1

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).

Community
  • 1
  • 1
diiN__________
  • 7,393
  • 6
  • 42
  • 69