Member Avatar for LeMa50

Well, I'm new here but you can halp me also.

I have pretty big problem. I need to store large text (more than 8000 charaters) in variable inside of procedure and then execute that query with EXEC command. Problem is that varchar supports up to 8000 characters and text and ntext cannot be declared inside procedure.

Is there any way to do this? BLOB? User defined types in MSSQL? Something?

Thx

  • 2 Contributors
  • 3 Replies
  • 192 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by dickersonka

Dani AI

Generated

Short answer: on SQL Server 2005 and later a procedure can declare varchar(max) or nvarchar(max) and hold far more than 8000 characters; use those types and prefer sp_executesql with parameters rather than embedding huge literals. This follows up on 's hint and addresses 's need to keep very long SQL in a variable and run it.

Practical pattern (safe, avoids literal-size limits and SQL injection):

-- build a long Unicode value in chunks, then pass it as a parameter
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM MyTable WHERE SomeCol = @p;';
DECLARE @longText NVARCHAR(MAX) = N'';
SET @longText = @longText + N'first chunk...';
SET @longText = @longText + N'second chunk...';  -- repeat as needed

EXEC sp_executesql @sql, N'@p NVARCHAR(MAX)', @p=@longText;

Notes, gotchas and troubleshooting:

  • SQL Server 2005+ is required for varchar(max) / nvarchar(max). Older servers (pre-2005) lack those types and need TEXT-based workarounds or an upgrade.
  • A single T‑SQL literal is limited (varchar literals ~8000 bytes, nvarchar literals ~4000 chars), so build long constants by concatenating chunks, selecting from a table, or by passing a parameter as above. Initialize string variables to '' (not NULL) before concatenation.
  • Use N'...' for Unicode literals to avoid implicit truncation when working with NVARCHAR.
  • Prefer sp_executesql with parameters for very long values — it avoids manual escaping of quotes, reduces injection risk, and allows plan reuse. If embedding text into @sql, ensure proper escaping (or better: don’t embed — pass as a parameter).
  • For binary/true BLOB needs, consider varbinary(max) (or FILESTREAM/FileTable on supported versions) rather than trying to shoehorn huge text into a varchar.

This approach solves the dynamic-SQL + long-text problem while keeping code safe and maintainable.

Recommended Answers

All 3 Replies

Member Avatar for dickersonka

you should be able to use a nvarchar(max)

Member Avatar for LeMa50

nvarchar(max) won't help. from what i have found so far there is possability that varchar uses more than 8000 characters.

thx anyway.

p.s. question still stands so you can reply :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.