stored procedure, 8000 character limit

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, 8000 character limit

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2006-02-23 : 19:34:57

I know this has been addressed in the dynamic crosstab forum but my procedure is different- I couldn't find the tidbit I was looking for there.
What I'm doing: Creating column heads out of the distinct values in one table and populating the columns with values also concatenated by a comma. So, the table below called tblSIFups will have an ItemName which will become the column head and any values in the ItemData will become the concatenated values in the column value. I will paste the code:
CREATE TABLE tblSIFups (
[FSID] [int] NOT NULL ,
[FID] [int] NULL ,
[ItemName] [varchar] (255) NULL ,
[ItemData] [varchar] (255) NULL ,
[ItemDescription] [nvarchar] (50) NULL
)
GO
sample data:
815177501 -2089251775 AIF surg date 12/13/1993
903276263 -2089251775 LE bg Yes
1101379650 -2089251775 Amp ambulatory Unk
1172069093 -2089251775 Amp healed Unk
1175076170 -2089251775 misc surg No

CREATE TABLE [tblFollowup2] (
[FID] [int] NOT NULL ,
[PID] [int] NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[Deceased] [nvarchar] (20) NULL ,
[Dateofdeath] [datetime] NULL ,
[Patent] [nvarchar] (20) NULL ,
[DateofFailure] [datetime] NULL
)
sample data:(tab delimited, does anyone want this in another form?)
-2137389682 1645278816 8/6/1994 NULL Yes 8/6/1994 Yes NULL
-2089251775 -164864753 9/13/1994 NULL No NULL Yes NULL

The stored procedure I am using builds a "create view" statement and calls a function to concatenate all the distinct ItemNames with a comma in between them. My problem is I'm dividing the variable that holds the concatenated values by a letter in the alphabet instead of the length of the character. I think I need to use the function that finds the length of the variable, then decides whether to create a new one, but I don't know how.... here is the procedure and the function....


CREATE PROCEDURE sp_Followup_crosstab AS

declare @Vals varchar(8000);
declare @Vals2 varchar(8000);

set @Vals = '';
set @Vals2 = ''; 

select @Vals = @Vals + ', dbo.List_FU(FID,''' + ItemName + ''') as '''+ ItemName + '''' from 
  (select distinct ItemName from tblSIFups where ItemName is not null  and ItemName < 'L%') t  
set @Vals = substring(@Vals,3,Len(@Vals)) 
select @Vals2 = @Vals2 + ', dbo.List_FU(FID,''' + ItemName + ''') as '''+ ItemName + '''' from 
  (select distinct ItemName from tblSIFups where ItemName is not null  and ItemName > 'L%') t  
set @Vals2=  substring(@Vals2,3,Len(@Vals2)) 

exec ( 'create view vwFollowup as  select pid, Date,Dateofdeath,DateofFailure,FID, ' +  @Vals +' ,  ' +  @Vals2  +  '  from (select pid, Date,Dateofdeath,DateofFailure,a.FID,ItemName, ItemData from tblSIFups a, tblFollowup2 b where a.FID=b.FID and ItemName is not null)  A 
 GROUP BY FID,pid, Date,Dateofdeath,DateofFailure')
GO

Here is the function....

CREATE function List_Fu(@FID int, @ItemName varchar(100))

returns varchar(8000)
as
begin
	declare @tmpStr varchar(8000)
	set @tmpStr=''

	Select @tmpStr = @tmpStr+',' + ItemData From tblSIFups
	where FID=@FID and ItemName = @ItemName
	order by ItemData
	-- strip a character off the beginning
	return substring(@tmpStr,2,8000)
end

Sorry this is so long, hope someone has the patience to read it. One more clarification: this does actually work but I think it's not going to work someday when one side of the alphabet produces more results than 8000 characters.
Thanks
Nicki

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources