CODEWITHSUNDEEP
sqlsql-serverdatabase
Nayan Rudani
Nayan Rudani

Reputation: 1189

sp_execute does not support dynamic SQL query with more than 4000 characters

I am using sp_execute statement to execute a dynamic SQL query.

But sp_execute does not support dynamic SQL query with more than 4000 characters.

It is working fine with the EXEC statement, though.

Please suggest if you have any solution.

This is my code:

declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)

select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
       @c=''' from sysobjects'

SELECT LEN(@a + @b + @c)

DECLARE @s NVARCHAR(max) = @a + @b + @c

EXEC sp_execute @s
EXEC(@s)

Upvotes: 0

Views: 1480

Answers (2)

JERRY
JERRY

Reputation: 1173

Use sp_executesql instead of sp_execute.

declare @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max)

select @d = 'a'
select @a = 'select top 1 name,''', @b = replicate(@d, 10000),
       @c=''' from sysobjects'

SELECT LEN(@a + @b + @c)

DECLARE @s NVARCHAR(max) = @a + @b + @c

EXEC sp_executesql @s
EXEC(@s)

Upvotes: 1

Squirrel
Squirrel

Reputation: 24783

You should use sp_executesql. It supports nvarchar(max). You may refer to here

But your issue is not so much with sp_execute but with the string @b. If you do notice, the following query gives len of 8036 and not 10036

SELECT len(@a+@b+@c)

The culprit is @b=replicate('a',10000) the length max at 8000. Replicate returns the same data type of a which is varchar

if you cast or convert() the string a to nvarchar(max), it will gives you 1000 characters

@b = replicate(convert(nvarchar(max), 'a'),10000)

or you may also try this

declare @d nvarchar(max)

select @d = 'a'
select @b = replicate(@d, 10000)
select len(@b)

Upvotes: 1

Related Questions