SQL Server: How to select data from executing dynamic SQL
In his post I will show you how to select data from executing dynamic SQL. This method is handy in a scenario where you plan to create a Function Import (using EntityFramework ) for a stored procedure that returns a result set based on the execution of a dynamic SQL. Capturing the result in a temp table or a table variable will allow EF to define the return type of the Function Import. In my example I am using table variables as they are easier to maintain than temp tables which have a physical presence. The following code returns one row of employee details that was insert into the table variable using dynamic SQL. Dynamic SQL can be executed using exec  statement or the sp_exectuesql system stored procedure.
declare @employee table(id int, name nvarchar(50)) declare @str nvarchar(1000) = 'select 1, ''Joe''' insert into @employee(id, name) exec(@str) select * from @employee
Here is another way to capture the output where sp_executesql is used to execute a dynamic sql string. The following code snippet returns the total numbers View in the database from the sys.objects object catalog view .
Declare @strSQL as nvarchar(100) Declare @strParam as nvarchar(100) Declare @XType as nvarchar(2) = 'V' Declare @rtnCount as varchar(100) Set @strSQL = 'Select @ObjCount=count(*) from Sysobjects Where xType=@xType' Set @strParam = '@ObjCount int output, @xType nvarchar(2)' execute sp_executesql @strSQL, @strParam, @rtnCount output ,@xType Select @rtnCount [View Count]