Execute sp_executesql

Question

Table called Emp contain id,name,lname,birthdate,address and salery .I want to select from emp. Basic query : select * from emp If pass the value for lname ,query : select * from emp where lname = 'fgfg' like this. So I created following sp.

create Procedure Proc_selectEmp
(
@name varchar(10) = null,
@lname varchar(10) = null,
@id varchar(10) = null

)
as
begin

select * from Emp
where 
(@name is null or name = @name) 
and (@lname is null or lname = @lname) 
and (@id is null or id = @id)
end

Like emp,there are 13 table having same column name. So my tablenmae is also dynamic.That's why, I choose execute sp_executesql.Can I create like this

create Procedure Proc_selectEmp
(
@name varchar(10) = null,
@lname varchar(10) = null,
@id varchar(10) = null
@tableName varchar(30)
)
as
begin
declare @query nvarchar(1000) 
set @query = @query +'select * from '+@tableName+'
where ('+@name+' is null or name = '+@name+') 
and ('+@lname+' is null or lname = '+@lname+') 
and ('+@id+' is null or id = '+@id+')
end'

execute sp_executesql @query

If the question was: "Can I build SQL dynamically?" then the answer is "Yes". — Germann Arlington, Sep 04 '12 at 08:11
But read up on SQL injection. And use `QUOTENAME`. See [The Curse and Blessings of Dynamic SQL - Dealing with Dynamic Table and Column Names](http://www.sommarskog.se/dynamic_sql.html#objectnames) — Martin Smith, Sep 04 '12 at 08:13

score 1 · Answer 1 · edited May 23 '17 at 11:58

It will work, although is pretty smelly, given that it requires that it requires that table name is a variable and thus tables must have the same column definitions.

Also, rather than including the @param is null or column = @param, rather, leave out unnecessary filters entirely, which is easy to do since you are using dynamic sql. This will avoid the parameter sniffing problem.

Lastly, instead of appending the column filters into the string, rather use the parameterized overload of sp_executesql, which will protect your from SQL injection attacks, and handle the escaping of quotes etc for you. Unfortunately, @tablename can't be parameterized, but hopefully? this isn't a user or foreign-supplied variable (in which case you will need to do some more thinking about design and or validation techniques).

i.e.

declare @query nvarchar(max) 
set @query = N'select * from ' + @tableName + N` where (1=1)`

if (@name is not null)
  set @query = @query + N'and name = @name'

-- ... same for @id and @lname

exec sp_executesql @SQL, 
     N'@name varchar(10),
       @lname varchar(10),
       @id varchar(10)',
       @name = @name,
       @lname = @lname,
       @id = @id

Edit Re : securing un-parameterizable inputs like table or column names in dynamic sql - see this post here for ideas - use of QUOTENAME and white-listing column / table names are prominent.

score -4 · Answer 2 · answered Sep 04 '12 at 08:13

-4

Yes you can but you have to write

EXECUTE(@query)

Instead of

execute sp_executesql @query

answered Sep 04 '12 at 08:13

Hassan

1,413
1
10
12

2

No you don't. `sp_executesql` is generally preferred as you can parameterise at least some parts. – Martin Smith Sep 04 '12 at 08:14
I am sorry but in his case he dont need it. Execute is perfect for his case. – Hassan Sep 04 '12 at 08:22
1

Your wording "*you have to write*" ... "*instead of*"... makes it seem like you are saying `sp_executesql` specifically *won't work*, which is patently false. – Andrew Barber Sep 04 '12 at 11:44
Sorry for the terms, but I am not English native :) – Hassan Sep 04 '12 at 11:57

Execute sp_executesql

2 Answers2