SQL Server Performance Forum – Threads Archive

EXEC String Exceed 4000 Characters

Hello Friends, I have following Dynamic Query. —————————— 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 ————————————————– @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 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=’

‘ />]

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

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

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, 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. Right now I am trying with FrankKalis advise. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

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 + ‘,[‘[email protected]+’ (‘+CAST(@MID as varchar)+’)]=min(CASE a.MenuItemId WHEN ‘+CAST(@MID as varchar)+’ THEN a.Permissions END)’
else
SELECT @SQL2 = @SQL2 + ‘,[‘[email protected]+’ (‘+CAST(@MID as varchar)+’)]=min(CASE a.MenuItemId WHEN ‘+CAST(@MID as varchar)+’ THEN a.Permissions END)’
else
SELECT @SQL1 = @SQL1 + ‘,[‘[email protected]+’ (‘+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

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

Probably because the IN clause is so large? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

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

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.

There are also several known problems with large IN clauses:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;815183
http://support.microsoft.com/default.aspx?scid=kb;EN-US;843534
http://support.microsoft.com/default.aspx?scid=kb;EN-US;885442 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Hi FrankKalis, Thank you for your links. It is really useful for me. Thank you all for contributing [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

What have you decided to do now? Madhivanan Failing to plan is Planning to fail

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

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> 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 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 Madhivanan Failing to plan is Planning to fail <hr height="1" noshade id="quote"></blockquote id="quote"> 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 ——————————————————————– DECLARE @deptID TABLE ( dept_id [int] ) INSERT INTO @deptID SELECT employee.deptid FROM from employee,department where employee.deptid = department.id group by employee.deptid order by employee.deptid DELETE FROM EMPLOYEE WHERE [deptid] IN (SELECT dept_id from @deptID) ———————————————————————– [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />][<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Also you can make use of Delete with Join and see if it makes better performance Madhivanan Failing to plan is Planning to fail

]]>