trying to execute this script gives only the first select as a result

declare @sp nvarchar(max)
select @sp = concat(N'select 1 ', replicate('-', 5000), char(13) + char(10), N'select 2')
exec (@sp)

but when I lower hyphens to 1000, I get also the second select How can I overcome this limit?


1 Answer 1


Your problem is with the CONCAT.

As documented, the return type depends on the type of the parameters. With a mixture of short nvarchar and varchar literal strings, your CONCAT will return nvarchar with some maximum length less than or equal to 4000.

A rewrite to use a max data type and consistent data types otherwise functions as you desire:

DECLARE @sp nvarchar(max);

        CONVERT(nvarchar(max), N'select 1 '), 
        REPLICATE(N'-', 5000), 
        NCHAR(13) + NCHAR(10), 
        N'select 2'

EXECUTE (@sp);

Online db<>fiddle demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.