Michael Sync

EXEC & EXEC sp_executesql

4 Jul, 2006 Microsoft SQL

EXEC and EXEC sp_executesql for Searching..


DECLARE @sql as nvarchar(100)
DECLARE @paraDOB datetime
SET @paraDOB = '1/1/1981'
SET @sql=N''
SET @sql=N'SELECT * FROM EmpMast'
SET @sql=@sql + ' WHERE DOB &gt;= @paraDOB'
exec sp_executesql @sql,N'@paraDOB datetime',@paraDOB</code>

Cool.. isn’t it?
But there are one limitation of using sql_executesql. As this statement accept the NVARCHAR which has only 4000 words capacity, the SQL string muct be less than 4000 words.

But I got the solution to solve this problem. By using EXEC with VARCHAR variable is the solution for this problem.

Here is the example.

DECLARE @sql as varchar(100)
DECLARE @paraDOB datetime
SET @paraDOB = '1/1/1981'
SET @sql=''
SET @sql='SELECT * FROM EmpMast'
SET @sql=@sql + ' WHERE DOB >= ''' + CONVERT(NVARCHAR,@paraDOB) + ''''
EXEC (@sql)      ---- Note: ( ) is important.

When we use sp_executesql, we can pass hidden parameter. sp_executesql’s performance is better than EXEC.

Note: some people used to pass all parameters if they are using sp_executesql statement. Actually, it is NOT necessary to pass all parameters. The parameters are only needed to be passed when the value of parameters are needed to get back.

7 Comments so far »

Acto said

am October 5 2006 @ 3:23 pm

Tip: Use NVARCHAR(MAX) in MS SQLserver 2005 - works like a charm
Michael Sync said

am October 5 2006 @ 3:31 pm

Thanks.. Acto.
Joaquin Quiros said

am April 4 2007 @ 11:22 pm

You saved my head today with this tip.
Thanx a lot!!!
Michael Sync said

am April 5 2007 @ 2:26 am

Your are very welcome. Joaquin.
Nuno Nogueira said

am November 27 2007 @ 10:14 am

Hi,

i have a sqlServer 2000 and i want to use exec with sp_executesql
butt i have more than 4000c.

I search and see:
>>EXEC(’EXEC sp_executesql N”’ + @sql1 + @sql2 + ”’,
>> N”@state char(2)”,
>> @state = ”’ + @state + ””)
>>This works, because the @stmt parameter to sp_executesql is >>ntext, so by itself, it does not have any limitation in size.

but doesn’t work…
any ideia?
Michael Sync said

am November 27 2007 @ 7:21 pm

Hi Nuno,

It works or doesn’t work?? I’m not clear what you are saying… What did you do with a lot of “>>” ??
Nuno Nogueira said

am November 28 2007 @ 2:43 am

hi,

the >> it’s to mark the example i see.
that example doesn’t work, at least for me…

either way, i change the sp_executesql to exec only, and it’s working. With exec() i dont have the 4000 limit characters.
something like

declare @strSql1 nvarchar(4000)
declare @strSql2 nvarchar(4000)
declare @strSql3 nvarchar(4000)

exec (@strSql1 + @strSql2 + @strSql3)

thx! anyway