Cursor issue- output seems lost in loop

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

SQL Server Development (2000)

Cursor issue- output seems lost in loop

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 11:09:41

Hello- I have a procedure that builds a string with a cursor- loops through the values in a temp table and concatenates onto my string those values. I seem to be able to print the variable with the cursor loop but outside of it, it prints blank... Any ideas? Thanks so much for any input. THis is the procedure, noted where it fails and succeeds to print.

CREATE PROCEDURE cross_list (
@Select varchar(1000)
)
AS
set nocount on
set ansi_warnings off

DECLARE @SQL varchar(8000);
DECLARE @ItemNAME varchar(150);

DECLARE C_ITEMNAME CURSOR FOR
SELECT DISTINCT ITEMNAME
FROM tblSIProcs2

SET @SQL = ' select PrID, '
OPEN C_ITEMNAME
FETCH NEXT FROM C_ITEMNAME INTO @ITEMNAME
WHILE @@FETCH_STATUS = 0
BEGIN

set @SQL= @SQL + ' dbo.List_SIProcs(prID, ''' + @ITEMNAME + ''') '

FETCH NEXT FROM C_ITEMNAME INTO @ITEMNAME
SET @SQL= @SQL + ','
--when i print @sql in here, it prints fine
END
print (@SQL) ---this does not print and no error- just blank
SET @SQL = LEFT(@SQL,LEN(@SQL)-1)

CLOSE C_ITEMNAME
DEALLOCATE C_ITEMNAME

---------------
-- RETURN DATA
---------------
--Here add the view creation, but also need column names
set @SQL=@SQL + 'from (' + @Select + ') A'
--print @SQL
--exec(@SQL)
GO

gpl
Posting Yak Master

195 Posts

Posted - 2005-05-16 : 11:29:47

I would guess that one of your values is NULL.
To test this, print the value of @ITEMNAME each time around the loop.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 11:30:17

I have found that this works for a smaller data set. But the @sql printed out in the loop is correct and under 5000 characters each time through for the larger data set.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 11:31:48

YES!! That is it- thank you so much gpl! Null values, makes tons of sense now that you pointed it out

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-16 : 11:43:03

you do know you don't need a cursor, right?


Declare @tmpStr varchar(100)

Select @tmpStr = isnull(@tmpStr+', ','') + ltrim(rtrim(RegionDescription)) From northwind.dbo.Region

Select @tmpStr

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 14:24:19

Well, the reason I have the cursor is that I need to group that by another ID. So, if it were an aggregate function if my field were a number and not a string, I would be doing
select count(ItemValue),ItemName,PRID from table group by ItemName, PRID
but I can't do that with the string- so I have a function List- that does what you are doing and then I have a cursor to do the other loop. It would be a loop in a loop otherwise, but I could only cut out one loop.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-17 : 10:09:28

I Just created a function using your code there and am trying to run this procedure but it truncates my string variable down to 4000 characters- do you know why? 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 prID=@prID and ItemName = @ItemName
order by ItemData
return @tmpStr
end

Thanks a lot

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-17 : 12:29:32

What makes you think it is being truncated? Do you expect @s to be more than 4000 characters?
Instead of
set @s = 'select prID, ' + substring(@s,3,8000) +
Try
set @s = 'select prID, ' + substring(@s,3,Len(@s)) +

*need more coffee*

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-17 : 12:33:52

Still truncates it. I think it's truncating it b/c it only prints out up to that amount- could be a setting in my browser. But also- i print out Len(@s) and it's 4000 so I don't think it's a client side issue. THanks a lot for the input.

and it is more than 4000, yes. when i use the cursor, which i was originally trying but was too slow, it's about 6000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources