0

I have a very long inline query that even when i set the nvarchar property to MAX the text gets truncated. Is there any way to make the sp_executesql to take a varchar instead.

When i try to change the nvarchar to varchar, i get an error, i have tried ntext too but same results.

Edit:

Based what i read on the question that were posted on the answer, this should work but it dosen't can anybody tell me why ?

    Declare @X varchar(MAX)
    SET @X = N'Select * From Users'
    Execute sp_executesql @X

While this works with no problems:

    Execute sp_executesql N'Select * From Users'
ykh
  • 1,775
  • 3
  • 31
  • 57
  • Same user as this? http://stackoverflow.com/q/14828534/27535 – gbn Feb 12 '13 at 08:49
  • `@X` has to be `nvarchar(max)`. Like this `Declare @X nvarchar(MAX)`. – Mikael Eriksson Feb 12 '13 at 10:17
  • the idea is putting the 'N' before the statement should make it work, that is what i understood event if it's varchar. – ykh Feb 12 '13 at 10:28
  • Nope. If you use a variable it has to be `nvarchar`. Your concatenating issues (truncation) could be fixed by using `@X = cast('' as nvarchar(max)) + N'select ' + etc `. The first cast on an empty string make sure that the concatenated string is `max`. – Mikael Eriksson Feb 12 '13 at 10:40

1 Answers1

1

No. sp_executesql requires nvarchar for the @stmt and @params parameters

Your parameters do not have to be nvarchar.

See Call sp_executesql with varchar parameter

If your nvarchar(max) is being truncated, you are concatenating wrong.
See For Nvarchar(Max) I am only getting 4000 characters in TSQL? for why

The point of sp_executesql is to avoid string building anyway: it allows you parametrise fixed queries for execution plan re-use. Why do you concatenate to build a string?

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Its a dynamic query, which join different strings together such as where and order by...etc. – ykh Feb 12 '13 at 09:03