SQL Server Performance

EXEC String Exceed 4000 Characters

Discussion in 'T-SQL Performance Tuning for Developers' started by knowledgebaseforbk, Feb 21, 2006.

  1. knowledgebaseforbk New Member

    Hello Friends,<br />I have following Dynamic Query.<br /><br />------------------------------<br /><br />DECLARE @return varchar(5000)<br />DECLARE @strSQL varchar(5000)<br />SELECT @return = CASE WHEN @return IS NULL THEN<br />'''' + convert(nvarchar(50),employee.deptid) + ''''<br />ELSE<br />'''' + convert(nvarchar(50),employee.deptid) + '''' + ',' + @return<br />END<br /> from employee,department where employee.deptid = department.id<br />group by employee.deptid<br />order by employee.deptid<br /><br />SET @strSQL = 'DELETE FROM EMPLOYEE WHERE [deptid] IN (' + @return + ')'<br />EXEC @strSQL<br />--------------------------------------------------<br /><br />@return is exceeding 4000 characters and when I execute EXEC @strSQL it is throwing error. I found from<a target="_blank" href=http://www.sommarskog.se/dynamic_sql.html#use-which>http://www.sommarskog.se/dynamic_sql.html#use-which</a> If strSQL exceeds 4000 characters use EXEC(@sql1 + @sql2). And also from the SQL Help, I found<br />If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string. How Can I Concatenate @strSQL into multiple local variables for above scenario(@sql1 and @sql2). Please advise me. Thank in Advance [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />
  2. FrankKalis Moderator

    You can have a look at the source code for sp_execresultset in master to see how Microsoft is doing this. [<img src='/community/emoticons/emotion-1.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 />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  3. ranjitjain New Member

    quote:Originally posted by knowledgebaseforbk
    DECLARE @return varchar(5000)
    DECLARE @strSQL varchar(5000)
    SELECT @return = CASE WHEN @return IS NULL THEN
    '''' + convert(nvarchar(50),employee.deptid) + ''''
    ELSE
    '''' + convert(nvarchar(50),employee.deptid) + '''' + ',' + @return
    END
    from employee,department where employee.deptid = department.id
    group by employee.deptid
    order by employee.deptid

    SET @strSQL = 'DELETE FROM EMPLOYEE WHERE [deptid] IN (' + @return + ')'
    EXEC @strSQL

    You have declared @return and @strsql as varchar with 5000 limit.
    It allows 8000 chars max. so you can even change that.
    Also in your select @return statement covert the deptid to varchar and not nvarchar as it will restrict @return to 4000.
    I mean this:
    SELECT @return = CASE WHEN @return IS NULL THEN
    '''' + convert(varchar(50),employee.deptid) + ''''
    ELSE
    '''' + convert(varchar(50),employee.deptid) + '''' + ',' + @return
    END
  4. knowledgebaseforbk New Member

    Hi RanjitJain,<br />sorry actually it is varchar only, but i typed wrongly as nvarchar(50). No problem with @return and @strSQL. it will allow 8000 characters I know. But the problem with EXEC @strSQL command. EXEC @strSQL as @strSQL is exceeding 4000 characters, it is throwing error. so i need to split @strSQL into two or more local variables. I am not getting the idea how to split @strSQL into two variables for my case.<br />Right now I am trying with FrankKalis advise. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. dineshasanka Moderator

    got this code from some where

    DECLARE @MID int
    , @SQL1 varchar(8000)
    , @SQL2 varchar(8000)
    , @SQL3 varchar(8000)
    , @SQL4 varchar(8000)
    , @mName varchar(500)

    DECLARE lcur_columns CURSOR FOR
    SELECT MenuItemId,MenuItemName FROM MenuItems a ORDER BY MenuItemID

    SELECT @SQL1 =
    'SELECT b.Company,a.UID,[ProjectName]=c.Name'

    open lcur_columns

    WHILE 1=1 BEGIN
    fetch next from lcur_columns into @MID,@mName
    if @@fetch_status <> 0 break

    If len(@SQL1) > 7000
    if len(@SQL2) > 7000
    SELECT @SQL3 = @SQL3 + ',['+@mName+' ('+CAST(@MID as varchar)+')]=min(CASE a.MenuItemId WHEN '+CAST(@MID as varchar)+' THEN a.Permissions END)'
    else
    SELECT @SQL2 = @SQL2 + ',['+@mName+' ('+CAST(@MID as varchar)+')]=min(CASE a.MenuItemId WHEN '+CAST(@MID as varchar)+' THEN a.Permissions END)'
    else
    SELECT @SQL1 = @SQL1 + ',['+@mName+' ('+CAST(@MID as varchar)+')]=min(CASE a.MenuItemId WHEN '+CAST(@MID as varchar)+' THEN a.Permissions END)'

    END

    select @SQL4 =
    ' INTO ##WHATEVER FROM ProjectUserMenuAccess a
    INNER JOIN USERS b ON a.UID = b.UID
    INNER JOIN PROJECTS c ON a.ProjectID = c.ProjectID
    INNER JOIN MenuItems d ON a.MenuItemId = d.MenuItemId
    Where c.status = 1 and b.status = 1
    GROUP BY a.UID, c.Name, b.Company
    order by a.UID'

    --print (len(@SQL))
    EXEC(@SQL1 + @SQL2 + @SQL3 + @SQL4)
    EXEC('select * from ##whatever')
    close lcur_columns
    deallocate lcur_columns

    drop table ##whatever

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  6. Madhivanan Moderator

    quote:SET @strSQL = 'DELETE FROM EMPLOYEE WHERE [deptid] IN (' + @return + ')'
    EXEC @strSQL
    Why do you need concatenation string to delete rows from a single table?

    Madhivanan

    Failing to plan is Planning to fail
  7. FrankKalis Moderator

    Probably because the IN clause is so large? [<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 />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
  8. Madhivanan Moderator

    In that case I prefer to insert those IN values in seperate table and delete using Inner join



    Declare @t table(i int, name varchar(100))
    insert into @t
    select 1,'test' union all
    select 2,'test' union all
    select 3,'test' union all
    select 4,'test' union all
    select 5,'test'

    Declare @In table(i int)
    insert into @In
    select 1 union all
    select 3 union all
    select 4

    select * from @t

    Delete t1 from @t t1 inner join @In T2
    on t1.i=T2.i

    Select * from @t

    Madhivanan

    Failing to plan is Planning to fail
  9. Adriaan New Member

    The string could probably be a lot smaller if he used a NOT IN clause.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-4.gif' alt=':p' />]
  10. knowledgebaseforbk New Member

    Hi Adriaan,
    I have given only simple example. Actually this problem occur in the production. I have taken simple employee table and have given example. If I use NOT IN clause also, it may exceed 4000 characters.FYI
  11. Adriaan New Member

    Hi Bharath,

    I haven't found the tongue-in-cheek smiley yet ...

    Anyway, Madhivanan's approach would be the best option here, but of course it depends on how you're interfacing with the SQL Server back-end whether you can make it work.
  12. FrankKalis Moderator

  13. knowledgebaseforbk New Member

    Hi FrankKalis,<br />Thank you for your links. It is really useful for me. Thank you all for contributing<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  14. Madhivanan Moderator

    What have you decided to do now?

    Madhivanan

    Failing to plan is Planning to fail
  15. knowledgebaseforbk New Member

    Hi Madhivanan,<br />I have done like this, it is working fine.<br /><br />---------------------------------------------------<br />DECLARE @return varchar(5000)<br />DECLARE @return1 varchar(5000)<br />declare @MAXBUFFLENint<br />SET @MAXBUFFLEN =4000<br /><br />SELECT @return = CASE WHEN @return IS NULL THEN<br />'''' + convert(varchar(50),employee.deptid) + ''''<br />ELSE<br />'''' + convert(varchar(50),employee.deptid) + '''' + ',' + @return<br />END<br />from employee,department where employee.deptid = department.id<br />group by employee.deptid<br />order by employee.deptid<br /><br />IF len(@return ) &gt; @MAXBUFFLEN <br />BEGIN<br />SET @return1 = RIGHT(@return ,len(@return ) - @MAXBUFFLEN)<br />SET @return = LEFT(@return , len(@return ) - len(@return1))<br />END<br />ELSE<br />BEGIN<br />SET @return1 =''<br />END<br />EXEC ('DELETE FROM EMPLOYEE WHERE [id] IN (' + @return + @return1 + ')') <br /><br />----------------------------------------------------------<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  16. knowledgebaseforbk New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />In that case I prefer to insert those IN values in seperate table and delete using Inner join<br /><br />Declare @t table(i int, name varchar(100))insert into @t select 1,'test' union allselect 2,'test' union allselect 3,'test' union allselect 4,'test' union allselect 5,'test' Declare @In table(i int)insert into @In select 1 union allselect 3 union allselect 4 select * from @tDelete t1 from @t t1 inner join @In T2on t1.i=T2.iSelect * from @t<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Thanks Madhivanan, I have tried with table data type. It is working fine. Thank you once again. I think it is very good one compare to previous one.I just want to share my solution to all<br /><br />--------------------------------------------------------------------<br />DECLARE @deptID TABLE<br />(<br />dept_id [int]<br />)<br />INSERT INTO @deptID <br />SELECT employee.deptid FROM<br />from employee,department where employee.deptid = department.id<br />group by employee.deptid<br />order by employee.deptid<br /><br />DELETE FROM EMPLOYEE WHERE [deptid] IN (SELECT dept_id from @deptID)<br />-----------------------------------------------------------------------<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />
  17. Madhivanan Moderator

    Also you can make use of Delete with Join and see if it makes better performance

    Madhivanan

    Failing to plan is Planning to fail

Share This Page