SQL Server Performance

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

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Oct 20, 2005.

  1. Cesar New Member

    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
  2. Madhivanan Moderator

    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+' '+@var2, @param_list, @offerDate output, @Id output, @TotalRegisters output

    Madhivanan

    Failing to plan is Planning to fail
  3. FrankKalis Moderator

    Have you tried this before posting? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  4. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Have you tried this before posting? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No. But I said it is General Approach [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Isnt it possible?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. FrankKalis Moderator

  6. Madhivanan Moderator

    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+' '+@var2)


    Madhivanan

    Failing to plan is Planning to fail
  7. Cesar New Member

    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">
  8. kpayne New Member

    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
  9. FrankKalis Moderator

    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)
  10. Cesar New Member

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

    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 +@sql2)

    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)
  12. Cesar New Member


    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...
  13. Madhivanan Moderator

    Put declare part inside @sql1

    Madhivanan

    Failing to plan is Planning to fail
  14. Cesar New Member

    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
  15. ranjitjain New Member

    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
  16. Cesar New Member


    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?
  17. Cesar New Member

    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=':D' />]<br /><br />Anyway, thank you for the link Frank

Share This Page