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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

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

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-10-20 : 07:23:44

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-20 : 07:27:41

Something similar to this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=52274

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources