Caveman's Blog

My commitment to learning.

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)
exec(@str)

select * from @employee

DNYSQL-1

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]

DNYSQL-2

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

Advertisements

Written by cavemansblog

May 22, 2013 at 12:13 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: