Dynamic SQL is too long to fit an nvarchar(4000)

I have a SP with Dynamic Sql that until now worked fine. But I made longer the Dynamic Sql code, and now the variable that holds the Dynamic code has not enough space to generate all the code. I mean that the variable @sql set to nvarchar(4000) isn#%92 t big enough to store/generate all the code. How can I solve this problem?

Here is a sample of my SP:


USE market
GO
ALTER PROCEDURE test_dyn6
@TotalRegisters nvarchar(100) output
As

BEGIN
SET NOCOUNT ON

DECLARE @sql nvarchar(4000), @param_list nvarchar(4000)

DECLARE @offerDate As nvarchar(100)
DECLARE @Id As nvarchar(1000)

SELECT @sql = ' SELECT @offerDate = offe.offerDate, @Id = offe.Offer_id
From Offers As offe
WHERE 1 = 1'

SELECT @sql = @sql + ' Order by offe.offerDate Desc, offe.Offer_id Desc'




SELECT @sql = @sql + ' SELECT offe.Offer_id, offe.User_num, offe.offerDate, offe.Offer_title
From Offers As offe
WHERE 1 = 1 AND ((offe.offerDate < @Data) Or (offe.offerDate = @Data And offe.Offer_id <= @Id))'

SELECT @sql = @sql + ' Order by offe.offerDate Desc, offe.Offer_id Desc'



SELECT @sql = @sql + ' SELECT @TotalRegisters = COUNT(*)
FROM Offers As offe
WHERE 1 = 1'



SELECT @param_list = '@offerDate datetime output, @Id nvarchar(1000) output,
@TotalRegisters nvarchar(100) output'

EXEC sp_executesql @sql, @param_list, @offerDate output, @Id output, @TotalRegisters output

SELECT @Data, @Id
SELECT @TotalRegisters

SET NOCOUNT OFF
END
GO

Thank you,
Cesar

Thank you for the specific solution in the article Frank, I tried to adapt my code to the example (although I don#%92 t understand all these quotes and some other parts of the code).<br /><br />Here is my first try to adapt my code: (Part in bold is changed code according to original posted code)<br /><br />Would you mind correct main mistakes and lacks before dive into Query Analyzer and see that nothing works, please? [<img src='/community/emoticons/emotion-1.gif' alt='' />] <br /><pre id="code"><font face="courier" size="2" id="code"><br />USE market<br />GO<br />ALTER PROCEDURE test_dyn6<br />@TotalRegisters nvarchar(100) output<br />As<br /><br />BEGIN <br /> SET NOCOUNT ON <br /><b><br /> CREATE TABLE #date_and_Id (offerDate datetime, offer_id bigint)<br /> <br /> DECLARE @sql1 nvarchar(4000),<br /> @sql2 nvarchar(4000),<br /> @offerDate nvarchar(100), @Id nvarchar(1000),<br /> @param_list nvarchar(4000)</b><br /><br /> <br /> SELECT <b>@sql1</b> = ' SELECT @offerDate = offe.offerDate, @Id = offe.Offer_id <br /> From Offers As offe<br /> WHERE 1 = 1'<br /> <br /> SELECT <b>@sql1</b> = <b>@sql1</b> + ' Order by offe.offerDate Desc, offe.Offer_id Desc' <br /><br /><br /> <br /><br /> SELECT <b>@sql2</b> = ' SELECT offe.Offer_id, offe.User_num, offe.offerDate, offe.Offer_title<br /> From Offers As offe <br /> WHERE 1 = 1 AND ((offe.offerDate &lt; @offerDate) Or (offe.offerDate = @offerDate And offe.Offer_id &lt;= @Id))' <br /><br /> SELECT <b>@sql2</b> = <b>@sql2</b> + ' Order by offe.offerDate Desc, offe.Offer_id Desc' <br /> <br /><br /><br /> SELECT <b>@sql2</b> = <b>@sql2</b> + ' SELECT @TotalRegisters = COUNT(*) <br /> FROM Offers As offe<br /> WHERE 1 = 1'<br /><br /><br /><br /> SELECT @param_list = '@offerDate datetime output, @Id nvarchar(1000) output,<br /> @TotalRegisters nvarchar(100) output'<br /><br /><br /><br /><b> <br />INSERT #date_and_Id (offerDate, offer_id)<br /> EXEC('DECLARE @offerDate nvarchar(100), @Id nvarchar(1000)<br /> EXEC sp_executesql N''' + @sql1 + @sql2 + @param_list + ''', <br /><br />N''@offerDate nvarchar(100) output, @Id nvarchar(1000) output, @TotalRegisters nvarchar(100) output'', @offerDate = @offerDate output, @Id = @Id output, @TotalRegisters = @TotalRegisters output <br /><br />Select @offerDate, @Id, @TotalRegisters#%92)</b> <br /><br /><br /> SET NOCOUNT OFF <br />END <br />GO</font id="code"></pre id="code">

Sometimes when I know that I am just over 4000 chars I use the following function to squeeze it into nvarchar(4000).



ALTER function udf_CompressQuery (
@QueryString varchar(8000)--/ The string that will be compressed.
) RETURNS nvarchar(4000) AS
begin

declare @Result nvarchar(4000)

set @QueryString = REPLACE(@QueryString,CHAR(9),' ')
set @QueryString = REPLACE(@QueryString,CHAR(10),' ')
set @QueryString = REPLACE(@QueryString,CHAR(13),' ')
set @QueryString = REPLACE(@QueryString,' ',' ')
set @QueryString = REPLACE(@QueryString,' ',' ')
set @QueryString = REPLACE(@QueryString,' ',' ')
set @QueryString = REPLACE(@QueryString,' ',' ')
set @QueryString = REPLACE(@QueryString,' ',' ')
set @QueryString = REPLACE(@QueryString,' ',' ')
set @Result = LEFT(REPLACE(@QueryString,' ',' '),4000)

return @Result
end


Keith Payne
Technical Marketing Solutions
www.tms-us.com

I don#%92 t know what you mean Frank, would you mind giving a simple example with my code please?

I tried this:



USE market
GO
ALTER PROCEDURE test_dyn6
@TotalRegisters nvarchar(100) output
As

BEGIN
SET NOCOUNT ON

CREATE TABLE #date_and_Id (offerDate datetime, offer_id varchar(10))

DECLARE @sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@offerDate nvarchar(100), @Id nvarchar(1000),
@param_list nvarchar(4000)


SELECT @sql1 = ' SELECT @offerDate = offe.offerDate, @Id = offe.Offer_id
From Offers As offe
WHERE 1 = 1'

SELECT @sql1 = @sql1 + ' Order by offe.offerDate Desc, offe.Offer_id Desc'




SELECT @sql2 = ' SELECT offe.Offer_id, offe.User_num, offe.offerDate, offe.Offer_title
From Offers As offe
WHERE 1 = 1 AND ((offe.offerDate < @offerDate) Or (offe.offerDate = @offerDate And offe.Offer_id <= @Id))'

SELECT @sql2 = @sql2 + ' Order by offe.offerDate Desc, offe.Offer_id Desc'



SELECT @sql2 = @sql2 + ' SELECT @TotalRegisters = COUNT(*)
FROM Offers As offe
WHERE 1 = 1'


EXEC('INSERT #date_and_Id (offerDate, offer_id) ' + @sql1 + @sql2)
SELECT * FROM #date_and_Id
DROP TABLE #date_and_Id


SET NOCOUNT OFF
END
GO

And I receive an error that says: You must declare ‘@offerDate#%92 variable. And I think it refers to the first ‘@offerDate#%92 in code, here:

...
SELECT @sql1 = ' SELECT @offerDate = offe.offerDate, @Id = offe.Offer_id
From Offers As offe
...

I don#%92 t understand why.. How I have to declare this variable? It is already declared...

If I put this:


SELECT @sql1 = ' DECLARE @offerDate nvarchar(100), @Id nvarchar(1000)
SELECT @offerDate = offe.offerDate, @Id = offe.Offer_id
From Offers As offe
WHERE 1 = 1'

I get the error: Incorrect syntax near of ‘DECLARE#%92

Try this eg: if it helps you

use northwind
go
declare @sql1 nvarchar(1000)
declare @count int
set @sql1='select @count=count(*) from products'
exec sp_executesql @sql1,N'@count int OUTPUT',@count OUTPUT
select @count

Hey! And what about this?:



USE market
GO
ALTER PROCEDURE test_dyn6
@TotalRegisters nvarchar(100) output
As

BEGIN
SET NOCOUNT ON

DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @sql3 nvarchar(4000) , @param_list nvarchar(4000)

DECLARE @offerDate As nvarchar(100)
DECLARE @Id As nvarchar(1000)

SELECT @sql1 = ' SELECT @offerDate = offe.offerDate, @Id = offe.Offer_id
From Offers As offe
WHERE 1 = 1'

SELECT @sql1 = @sql1 + ' Order by offe.offerDate Desc, offe.Offer_id Desc'




SELECT @sql2 = ' SELECT offe.Offer_id, offe.User_num, offe.offerDate, offe.Offer_title
From Offers As offe
WHERE 1 = 1 AND ((offe.offerDate < @Data) Or (offe.offerDate = @Data And offe.Offer_id <= @Id))'

SELECT @sql2 = @sql2 + ' Order by offe.offerDate Desc, offe.Offer_id Desc'



SELECT @sql3 = ' SELECT @TotalRegisters = COUNT(*)
FROM Offers As offe
WHERE 1 = 1'



SELECT @param_list = '@offerDate datetime output, @Id nvarchar(1000) output, @TotalRegisters nvarchar(100) output'

EXEC sp_executesql @sql1, @param_list, @offerDate output, @Id output, @TotalRegisters output

EXEC sp_executesql @sql2, @param_list, @offerDate output, @Id output, @TotalRegisters output

EXEC sp_executesql @sql3, @param_list, @offerDate output, @Id output, @TotalRegisters output

SELECT @Data, @Id
SELECT @TotalRegisters

SET NOCOUNT OFF
END
GO

It works fine in my Query Analyzer, and perhaps it is a better solution than the referenced articlehttp://www.sommarskog.se/dynamic_sql.html#use-which

What do you think?

Well.., indeed I see that it doesn' t work in my complete implemented code. So, I have to return to referenced article [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Anyway, thank you for the link Frank