Question

  Asked: Feb 6 2008   4:59 PM GMT
  Asked by: Aabbccddee


using a local variable to build an sql-statement beyond datatype nvarchar(4000) and execute with sp_executesql


SQL, SQL Server

I build a sql-statement and put it in a local variable so i can execute it using sp_executesql, but a local variable cannot be a text or ntext data type, and a varchar data type cannot be used by sp_executesql. So the maximum lenght of the statement cannot be more than nvarchar(4000)...
is there any work around?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



Pretty much no. I ran into this same problem with some dynamic SQL that we had, and I had to remove some of the code from the dynamic SQL and pass it's results into the sp_executesql via the parameter.

Do as much of the logic as you can outside of the dynamic SQL so that only the final select statement is dynamic.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Development and SQL Server.

Looking for relevant Development Whitepapers? Visit the SearchWinDevelopment.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Mrdenny  |   Feb 6 2008  5:32PM GMT

Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.