SQL Server Performance Forum – Threads Archive

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

General approach is to split it to multiple variables Declare @var1 nvarchar(4000)
Declare @var2 nvarchar(4000) Assign first 4000 chars to @var1 and rest 4000 to @var2 then combine them like EXEC sp_executesql @var1+’ ‘[email protected], @param_list, @offerDate output, @Id output, @TotalRegisters output Madhivanan Failing to plan is Planning to fail

Have you tried this before posting? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Have you tried this before posting? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <hr height="1" noshade id="quote"></blockquote id="quote"> No. But I said it is General Approach [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Isnt it possible? Madhivanan Failing to plan is Planning to fail

No.http://www.sommarskog.se/dynamic_sql.html#use-which —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Well.
Once I did something like this with Exec and thought that can be also done with sp_executesql Declare @var1 varchar(20)
Declare @var2 varchar(20)
set @var1=’Select * from’
set @var2=’northwind..customers’
Exec(@var1+’ ‘[email protected])
Madhivanan Failing to plan is Planning to fail

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). Here is my first try to adapt my code: (Part in bold is changed code according to original posted code) 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=’

‘ />] <pre id="code"> 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 bigint) 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′ SELECT @param_list = ‘@offerDate datetime output, @Id nvarchar(1000) output, @TotalRegisters nvarchar(100) output' INSERT #date_and_Id (offerDate, offer_id) EXEC(‘DECLARE @offerDate nvarchar(100), @Id nvarchar(1000) EXEC sp_executesql N”’ + @sql1 + @sql2 + @param_list + ”’, N”@offerDate nvarchar(100) output, @Id nvarchar(1000) output, @TotalRegisters nvarchar(100) output”, @offerDate = @offerDate output, @Id = @Id output, @TotalRegisters = @TotalRegisters output Select @offerDate, @Id, @TotalRegisters#%92) SET NOCOUNT OFF END GO</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

Cesar, I don’t think it will work this way. I think you need to place everything (including the INSERT statement) in a string and execute that string.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

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

Here’s a quick example
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 OrderDate, CustomerID FROM Northwind..Orders’
SELECT @sql2 = ‘ WHERE 1 = 1 AND OrderDate <=”19960710”’ EXEC(‘INSERT #date_and_Id (offerDate, offer_id) ‘ + @sql1 [email protected]) SELECT *
FROM #date_and_Id DROP TABLE #date_and_Id Make sure you read closely through the article I’ve referenced.http://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

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…

Put declare part inside @sql1 Madhivanan Failing to plan is Planning to fail

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=’

‘ />] Anyway, thank you for the link Frank

]]>