EXEC & EXEC sp_executesql
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 >= @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.
Tip: Use NVARCHAR(MAX) in MS SQLserver 2005 – works like a charm
Thanks.. Acto.
You saved my head today with this tip.
Thanx a lot!!!
:)
Your are very welcome. Joaquin. :)
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?
Hi Nuno,
It works or doesn’t work?? I’m not clear what you are saying… What did you do with a lot of “>>” ??
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
Wrong!
Don’t teach yourself the wrong solutions.
If you chooce for sp_execute, then there is a reason for it, and then don’t switch to EXEC (because it is somewhere in a blog or forum).
Please first read the SQL / DBA must read(s) from http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong
There is the clean solution. (or switch to SQL2005/2008 and higher).
The solution Erland Sommarskog gives (hopefully I may quote him) is:
DECLARE @sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@state char(2)
SELECT @state = ‘CA’
SELECT @sql1 = N’SELECT COUNT(*)’
SELECT @sql2 = N’FROM dbo.authors WHERE state = @state’
EXEC(‘EXEC sp_executesql N”’ + @sql1 + @sql2 + ”’,
N”@state char(2)”,
@state = ”’ + @state + ””)
- voila – optimized queryplan, parameters outside SQL.
Hurray!
Hi Vincent,
Can you point me the wrong point?
This post is for SQL 2000.. It’s the way that I used in my real projects and I’m very sure that it is working fine.. And also, I’m very very sure that it is not wrong.