Dynamic sql execution – EXEC vs sp_executesql

The only way of executing dynamic sql is thru the usage of methods EXEC and sp_executesql. In this post we will explore the differences between the two.

 

EXEC is not parameterised

EXEC(‘select 1’)

SP_executesql is parameterised

EXEC sp_executesql N'select 1'

Let us create this simple data set

use tempdb

create table customer (cust_id int, cust_name varchar(100))
insert into customer(cust_id,cust_name)
select 1,'testing'

Now assume that you want to pass table name, column name and value for the column to get the data

It can be done in dynamic sql in the following methods

Method 1 EXEC

Declare @sql varchar(1000)
Declare @table_name varchar(100), @col_name varchar(20)='cust_id',@col_value int=1
Select @sql='', @table_name='customer'
Set @sql='select * from '+@table_name+' where '+@col_name+' = '+cast(@col_value as varchar(10))
Exec(@sql)

The above method is not parameterised and the sql query is constructed and executed using EXEC

Method 2 sp_executesql


Declare @sql nvarchar(1000)
Declare @table_name varchar(100), @col_name varchar(20)='cust_id',@col_value int=1
Select @sql='', @table_name='customer'
set @sql='select * from '+ @table_name +' where '+@col_name+'=@col_value'
exec sp_executesql @sql,N' @col_value int',@col_value 

The above method is parameterised and the sql query is constructed and executed via parameter using sp_executesql

Both of them produce the following result

cust_id     cust_name
----------- ----------------
1           testing

In the next post we will explore SQL Injection

Advertisements

One thought on “Dynamic sql execution – EXEC vs sp_executesql

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