Reputation: 3365
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
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
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