sp_executesql -> nvarchar not BIG enough

  • I'm trying to create a Stored Procedure...

    CREATE PROCEDURE [dbo].[R_WhseOrders]

     @Date1  datetime

    AS

    --SET NOCOUNT ON

    DECLARE

     @sql  nvarchar(4000),

     @param nvarchar(25)

    Select @sql = ......blaa blaaa (way way over 4000 characters)....

    SELECT @param =

          '@xDate1  datetime'

      EXEC sp_executesql @sql, @param, @Date1

     

    My @sql is around 8000 in size and sp_executesql can only take nvarchar which will only allow 4000 in size.  Is there a work around for this??

  • Nevermind.

    workaround

     

       EXEC('EXEC sp_executesql N''' + @sql1 +'',''+ @sql2 + @sql3 ''', N''' + @param +''', '''+ @Date1 +'''')

  • The best time for finding the answer to a question is about 10 minutes after posting it to a forum. I'm glad it's not just me!

    --
    Scott

  • SLBt

    As you may know, sp_executesql implicitly converts [whatever you send to it as a statement] to NTEXT. While you can't declare a local variable as NTEXT, you -can- declare an NTEXT parameter for a stored procedure.

    A simple proc to demo this is

    CREATE PROCEDURE slbt_texttest @stmt NTEXT AS

    EXEC sp_executesql @stmt

    (Of course, you have to be careful where you place single and double quotes)

    I executed the procedure through Query Analyzer and it seemed to work OK:

    EXEC slbt_texttest ' '

    Cheers,

    Ken

  • If the text is around 8000 characters than you can decalre the @stmt VARCHAR(8000)

    AND use EXEC(@stmt) as well.

    Prasad Bhogadi
    www.inforaise.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply