EXEC & EXEC sp_executesql 9

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.

9 thoughts on “EXEC & EXEC sp_executesql

  1. Reply Acto Oct 5,2006 3:23 pm

    Tip: Use NVARCHAR(MAX) in MS SQLserver 2005 – works like a charm

  2. Reply Joaquin Quiros Apr 4,2007 11:22 pm

    You saved my head today with this tip.
    Thanx a lot!!!
    :)

  3. Reply Michael Sync Apr 5,2007 2:26 am

    Your are very welcome. Joaquin. :)

  4. Reply Nuno Nogueira Nov 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?

  5. Reply Michael Sync Nov 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 “>>” ??

  6. Reply Nuno Nogueira Nov 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

  7. Reply Vincent van Witteloostuyn Aug 15,2008 6:30 am

    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!

  8. Reply Michael Sync Aug 15,2008 7:36 am

    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.

Leave a Reply