Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to run a more than 8000 characters SQL statement from a variable?

Tags:

I can use the following code for tiny little queries:

DECLARE @sql VARCHAR(8000) SET @sql = 'SELECT * FROM myTable' Exec @sql 

The above method is very useful in order to maintain large amounts of code, especially when we need to make changes once and have them reflected everywhere.

My problem is my query (it's only one single query) that I want to feed into the @sql variable uses more than 25 table joins, some of them on temporary table variables, incorporates complex operations and it is hence much more than 8000 characters long.

I wished to use TEXT data type to store this query, but MSDN shows a warning message that Microsoft is planning to remove Text, NText and Image data types from their next versions. I wish my code to run in future too.

I thought of storing this query in a separate file, but as it uses joins on table variables and other procedure-specific parameters, I doubt if this is possible.

Kindly tell me a method to store a large query into a variable and execute it multiple times in a procedure.

like image 736
Rachcha Avatar asked Feb 07 '12 07:02

Rachcha


People also ask

How can I use more than 8000 characters in SQL Server?

You still Cannot have a Single Unbroken Literal String Larger than 8000 (or 4000 for nVarChar). Literal Strings are those you hard-code and wrap in apostrophe's. You must Break those Strings up or SQL Server will Truncate each one BEFORE concatenating.

How do I store more than 4000 characters in SQL?

When dealing with an NVARCHAR(MAX) variable, you have to explicitly cast any literals or expressions that you are concatenating to be NVARCHAR(MAX). Ivo, The following is DEMO ONLY T-SQL script, no business meaning. It is over 4000 bytes dynamic SQL and works!

How do I increase the number of characters in SQL?

Right click in the query editor and select Query Options. Under Results, select Text. Uncheck “Include column headers in the result set” and change the maximum number of characters displayed to 8192. Click on in the editor and click Results To and choose Results to Text and click OK.


2 Answers

The problem is with implicit conversion.

If you have Unicode/nChar/nVarChar values you are concatenating, then SQL Server will implicitly convert your string to VarChar(8000), and it is unfortunately too dumb to realize it will truncate your string or even give you a Warning that data has been truncated for that matter!

When concatenating long strings (or strings that you feel could be long) always pre-concatenate your string building with CAST('' as nVarChar(MAX)) like so:

SET @Query = CAST('' as nVarChar(MAX))--Force implicit conversion to nVarChar(MAX)            + 'SELECT...'-- some of the query gets set here            + '...'-- more query gets added on, etc. 

What a pain and scary to think this is just how SQL Server works. :(

I know other workarounds on the web say to break up your code into multiple SET/SELECT assignments using multiple variables, but this is unnecessary given the solution above.

For those who hit a 4000 character max, it was probably because you had Unicode so it was implicitly converted to nVarChar(4000).

Warning:
You still Cannot have a Single Unbroken Literal String Larger than 8000 (or 4000 for nVarChar).
Literal Strings are those you hard-code and wrap in apostrophe's.
You must Break those Strings up or SQL Server will Truncate each one BEFORE concatenating.
I add ' + ' every 20 lines (or so) to make sure I do not go over.
That's an average of at most 200 characters per line - but remember, spaces still count!

Explanation:
What's happening behind the scenes is that even though the variable you are assigning to uses (MAX), SQL Server will evaluate the right-hand side of the value you are assigning first and default to nVarChar(4000) or VarChar(8000) (depending on what you're concatenating). After it is done figuring out the value (and after truncating it for you) it then converts it to (MAX) when assigning it to your variable, but by then it is too late.

like image 117
MikeTeeVee Avatar answered Oct 24 '22 23:10

MikeTeeVee


If you are on SQL Server 2008 or newer you can use VARCHAR(MAX)

DECLARE @sql VARCHAR(MAX) 
like image 29
Andrea Colleoni Avatar answered Oct 25 '22 00:10

Andrea Colleoni