We are writing a stored procedure responsible for get a stored procedure name and returning a result containing the stored procedure columns and their data types. However, we bumped into a problem executing a dynamic query to return the results of stored procedure, but we can't store it in a temp table!
You can see our query below:
DECLARE @ProcName VARCHAR(100)='spGetOraganizationsList', @ParamName VARCHAR(100),@DataType VARCHAR(20), @Query NVARCHAR(MAX)='EXEC '+'spGetOraganizationsList ' SELECT PARAMETER_NAME,DATA_TYPE INTO #Tmp FROM information_schema.PARAMETERS WHERE [email protected] DECLARE ParamCursor CURSOR FOR SELECT * FROM #Tmp OPEN ParamCursor FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType WHILE @@FETCH_STATUS = 0 BEGIN SET @[email protected][email protected]+'=Null,' FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType END CLOSE ParamCursor DEALLOCATE ParamCursor DROP TABLE #Tmp EXEC sp_executesql @Query
The thing is I can't store the results of it in a temp table,
OPENROWSET does not accept variables.