in Microsoft SQL

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.

Leave a Reply

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

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

  3. 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?

  4. 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

  5. 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!

  6. 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.