Slow cursor and failing replacement program | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slow cursor and failing replacement program

Hi- I am trying to replace a program that I wrote with a cursor that runs way too slow with this below- unfortunately it truncates the variable that I am building with a function also below (the cursor does not do that). Maybe I should go back to the cursor method and figure out why it’s too slow? I’ll give this one another shot in case there is something glaringly wrong…
It truncates my string variable @s down to 4000 characters- Here is the code: CREATE PROCEDURE list_table2 AS
declare @s varchar(8000)
set @s = ”
select @s = @s + ‘, dbo.List_SIProcs(prID,”’ + ItemName + ”’)’ from
(select distinct ItemName from tblSIProcs2 where ItemName is not null) t
–print Len(@s) –4000
print @s
set @s = ‘select prID, ‘ + substring(@s,3,8000) + ‘ from (select PRID from tblSIProcs2 where PRID is not null) A
GROUP BY PRID’
–print (@s)
–exec (@s)
GO The function is :
CREATE function List_SIProcs(@prID int, @ItemName varchar(10))
returns varchar(100)
as
begin
declare @tmpStr varchar(100)
set @tmpStr=”
Select @tmpStr = isnull(@tmpStr+’, ‘,”) + ltrim(rtrim(ItemData)) From tblSIProcs2
where [email protected] and ItemName = @ItemName
order by ItemData
return @tmpStr
end Thanks a lot Oh- here is the table structure- the problem with this sample data is that it works- the larger amound of data is what is being truncated.
create table tblSProcs2 (PrID int,ItemName varchar(10), ItemData varchar(10))
insert into tblSProcs2
select 1, ‘meds’,’asprin’
union all select 2,’meds’,’asprin’
union all select 2,’meds’,’ibuprofen’
union all select 3,’meds’,’bayer’
union all select 3,’meds’,’asprin’
union all select 3,’meds’,’ibuprofen’
union all select 3,’area’,’arm’
union all select 2,’area’,’leg’

]]>