EXEC String Exceed 4000 Characters

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

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

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='' />]

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='' />]

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

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.

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='' />]

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='' />]

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