EXEC [sp_executesql] to run multiple Dynamic SQL Strings that are very large

Question

So I have split up my Dynamic SQL Strings into @SQLQuery1 and @SQLQuery2 because they are huge. I was using just one @SQLQuery1 when I saw that it was getting cut off because of volume and space limitations...so I split it up and now it looks good as the first part in @SQLQuery1 and a separate SELECT in @SQLQuery2.

Is there any way that I can now get EXEC [sp_executesql] to run BOTH parts so that it results in just one result set?

I know if I try to CONCAT @SQLQuery1 and @SQLQuery2 I am going to run into the same length limitations.

Do I really have to run these separately and then gather all the results rather than getting one large result set?

Thanks in advance for your review and am hopeful for a reply.

Answer 1

You need to feed sp_executesql a single SQL string, and there is no need to split it up. Just make sure that you declare @SQLQuery as nvarchar(MAX) - that fits 2GB of data. Your strings are not that long, I hope!

One thing that can bite you is if you do:

DECLARE @sql nvarchar(MAX) = N'3500 long string' + @shortstring+ N'Another 3500 long string'

Because all strings in the expression are below the 4000 limit, the data type of the expression is nvarchar(4000), so there will be truncation. The remedy is do something like this:

DECLARE @sql nvarchar(MAX) = cast(N'' AS nvarchar(MAX)) + N'3500 long string' + @shortstring+ N'Another 3500 long string'

Now all strings will be converted to nvarchar(MAX), and the data type of the expression will be nvarchar(MAX), and there will be no truncation.

Answer 2

Hi @Bobby P

The EXEC command has no limitation to the length of the command, but sp_executesql has a parameter that has a limit.

How about using EXEC (@SQLQuery1 +' UNION '+ @SQLQuery2) instead of EXEC [sp_executesql]?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.