Caveman's Blog

My commitment to learning.

Posts Tagged ‘sys.objects

SQL Server: How to select data from executing dynamic SQL

leave a comment »

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 [2]) 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 [3] 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)

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 [4].

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]


1. sp_executesql (Transact-SQL)
2. Entity Framework
3. EXECUTE (Transact-SQL)
4. Object Catalog Views (Transact-SQL)

Written by cavemansblog

May 22, 2013 at 12:13 pm