Stored procedure truncating string variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure truncating string variable

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-19 : 13:33:25

I posted this in the developer forum but I think it belongs here- sorry if a repeat.
I have this stored procedure for data such as the following:
create table tblSIProcs2 (PrID int,ItemName varchar(10), ItemData varchar(10))
insert into tblSIProcs2
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'
drop table tblSIProcs2

The problem is that data will work fine but when I use my huge table of data, the @sql variable is truncated to 4000 characters.

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 prID=@prID and ItemName = @ItemName
order by ItemData
return @tmpStr
end

Thanks a lot if anyone can help.
Nicki

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-19 : 13:43:34

It's nothing to do with the function because you aren't calling it to build @s.
It would happen if it was trying to work with nvarchars.
Is ItemName really a varchar or nvarchar?
try
select @s = @s + ', dbo.List_SIProcs(prID,''' + convert(varchar(100),ItemName) + ''')' from

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-19 : 14:55:42

It is nvarchar. I didn't know that was a problem- I will try this and let you know. Thank you.

Ok- I ran it this way and it finishes but I get no data- the columns all have blank values. It's the correct amount of columns though. I'll work with it- ItemName and ItemData are both nvararchar, so maybe both need converting

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-19 : 15:45:34

So, this is great- it's no longer truncating the variable. I am not however getting values returned from my function any more though. Any ideas on that one? The statement looks good, but returns blank in all the values when it used to return stuff. I'm also running one with more diverse data but it's been running for a half hour and not done yet. Make that 1.5 hours

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-19 : 17:01:33

From the look of this I would expect it to take a long time for any sizable table. Just calling a single function would be slow but calling it lots of times for each row is likely to be a problem.
Maybe you have a lot of ItemName's that aren't applicable to all prID's so return blanks.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-20 : 09:19:42

It took over 5 hours and that was just half the data. Actually, there are no ItemNames that have blank ItemDatas and I filtered out a certain group of PRIDs that would have data. The places where there is data, there's a comma ahead of the data and only one value. It's pretty screwy. I wonder if I shouldn't try the cursor I was originally using- do you think that would be slower than this or faster? I also don't have any indexes on it.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-20 : 09:36:16

I tried to run this- created this sample data
and ran the code at the bottom (I've just copied the actual sql statement as it would be created in my procedure). This also does not give results. Is there something wrong with this data do you think?
create table tmp4 (PrID int,ItemName varchar(50), ItemData varchar(40))
insert into tmp4
select '1462910863', 'Debridement Location','N/A'
union all select '1462910863','Debridement Location','foot'
union all select '1024404175','Debridement Location','N/A'
union all select '684919116','Debridement Location','toe(s)'

--Stored procedure:
CREATE procedure sp_MakeCharList
AS
select prID, dbo.List_SIProcs(prID,'Debridement Location')
from (select PRID from tmp4 where PRID in ('1462910863','1024404175','684919116')) A
GROUP BY PRID
GO

---function:
CREATE function List_SIProcs(@prID int, @ItemName varchar(10))
returns varchar(8000)
as
begin
declare @tmpStr varchar(8000)
set @tmpStr=''
Select @tmpStr = isnull(@tmpStr+', ','') + ltrim(rtrim(ItemData)) From tmp4
where prID=@prID and ItemName = @ItemName
order by ItemData
return @tmpStr
end

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-20 : 10:11:52

Sorry- embarssing- I had the wrong varchar in function- it should match the procedure. THat explains that but it still slow as molassis. Hmm. Thanks for the help.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-20 : 10:35:36

I created an index on PrId and ItemName (which is a column holding very few values repeated over and over) - both of those are in the where clause in my function which is called a lot. The time it took now is 17 minutes. Rather than 5 hours. Seems incredible. I also changed the varchar(10) to 50- I wonder if that slowed it down, the constant failure. Interesting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources