CODEWITHSUNDEEP
sqlsql-server
Shankar
Shankar

Reputation: 45

Get result to temptable by execute OPENROWSET stored procedures with dynamic parameters in SQL?

I am using SQL Server 2012, I need result to temp-table by execute stored procedure with dynamic parameter values.

Below I show my code, while I execute code I get an error, so is this possible to get output with dynamic parameters while execute stored procedures in openrowset or something like this?

SET @sql = N'SELECT * INTO #TempStamp_Status       
             FROM OPENROWSET(''SQLNCLI'',
        ''Admin-SERVER\SQLEXPRESS'' ; ''sa'' ; ''123456''
        ''SET NOCOUNT ON;SET FMTONLY OFF;exec 
RDS_DS.dbo.Qry_GetParticipationDetails ' + cast( @ResidentId AS 
NVARCHAR(10))   + ',''' +CAST(@StartDate AS NVARCHAR(100)) +''',''' 
+CAST(@EndDate AS NVARCHAR(100)) +''')''';

When I execute this SQL query, I get this error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'SET NOCOUNT ON;SET FMTONLY OFF;exec RDS_DS.dbo.Qry_GetParticipationDetails 52,'

Thanks in advance

Upvotes: 0

Views: 595

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

Missing quotes. Could you try this?

SET @sql = N'SELECT * INTO #TempStamp_Status       
             FROM OPENROWSET(''SQLNCLI'',
        ''Admin-SERVER\SQLEXPRESS'' ; ''sa'' ; ''123456'',
        ''SET NOCOUNT ON;SET FMTONLY OFF;exec 
RDS_DS.dbo.Qry_GetParticipationDetails ' + cast( @ResidentId AS 
NVARCHAR(10))   + ',''''' +CAST(@StartDate AS NVARCHAR(100)) +''''',''''' 
+CAST(@EndDate AS NVARCHAR(100)) +''''''')';

Upvotes: 2

Related Questions