Syntax error executing stored procedure in openrowset with parameters

Question

I'm trying to store the result of a stored procedure in a temp table, therefore I have to call it using OPENROWSET however when executing the query

DECLARE @sql nvarchar(MAX)      

DECLARE @callToProc nvarchar(255)
SET @callToProc =  'EXEC dbo.mySpName @param1=' + CAST ( 1 AS nvarchar(200) ) + ', @param2= ''Achat'' ' 

SET @sql =  'SELECT * INTO #mytempTab FROM OPENROWSET(''SQLNCLI'', ''Server=myserv;Trusted_Connection=yes;'', ' + @callToProc + ') AS myalias'

EXECUTE(@sql)

I get the following error :

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'EXEC'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

But I really don't see what is wrong with my request

Thanks !

marc_s · Accepted Answer · 2014-11-27 15:44:25Z

1

Thats not the right way of doing it. Try something like this. This method can employed if you don't know the no. of columns returned by the stored procedure.

DECLARE @sql NVARCHAR(MAX)
DECLARE @callToProc NVARCHAR(255)

SET @callToProc = '''EXEC dbo.sp_RecupererMontantLegsParRunSoitVenteSoitAchat @IdRun='+ Cast ( 1 AS NVARCHAR(200) )+ ', @TypeLeg= ''''Achat'''''' '

SET @sql = 'SELECT * INTO #mytempTab FROM OPENROWSET(''SQLNCLI'', ''Server=myserv;Trusted_Connection=yes;'', '
           + @callToProc + ') AS myalias'

EXECUTE(@sql)

NOTE : To debug dynamic sql always print the the dynamic sql before executing which will give you an idea of what is wrong.

edited Nov 27, 2014 at 15:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 27, 2014 at 15:30

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tobiak777 Over a year ago

Thank you very much, this solved my problem (quotation marks were missing). However I also realize that I should use the method provided by Andy Korneyev since I know the columns returned by my SP. Thanks a lot for the tip about debugging dynamic sql.

Andrey Korneyev · Accepted Answer · 2014-11-27 15:31:31Z

1

You can do it without using OPENROWSET at all.

Just create your temp table having the same quantity, order and types of columns as your stored procedure returns, and then just insert into it like this:

create table #myTempTable ( columns definitions here)

insert into #mytempTab
exec dbo.mySpName
    @Param1 = ...

answered Nov 27, 2014 at 15:31

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

1 Comment

tobiak777 Over a year ago

Thank you very much, I'll move toward this approach.

Collectives™ on Stack Overflow

Syntax error executing stored procedure in openrowset with parameters

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related