CODEWITHSUNDEEP
sql-servert-sql
tobiak777
tobiak777

Reputation: 3365

Syntax error executing stored procedure in openrowset with parameters

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 !

Upvotes: 0

Views: 953

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

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.

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26896

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

Upvotes: 1

Related Questions