Recursive function - SQL Server Forums

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)

Recursive function

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-11-01 : 14:44:11

i have a table

CREATE TABLE [dbo].[HCPCS_2012](
[HCPCS] [varchar](5) NULL,
[Seq_Num] [int] NULL,
[Long_Description] [varchar](1000) NULL
) ON [PRIMARY]

insert into HCPCS_2012 select 'A0021', 100, 'AMBULANCE SERVICE, OUTSIDE STATE PER MILE, TRANSPORT (MEDICAID ONLY)'
insert into HCPCS_2012 select 'A0080', 100, 'NON-EMERGENCY TRANSPORTATION, PER MILE - VEHICLE PROVIDED BY VOLUNTEER'
insert into HCPCS_2012 select 'A0080', 200, '(INDIVIDUAL OR ORGANIZATION), WITH NO VESTED INTEREST'
insert into HCPCS_2012 select 'A0090', 100, 'NON-EMERGENCY TRANSPORTATION, PER MILE - VEHICLE PROVIDED BY INDIVIDUAL (FAMILY'
insert into HCPCS_2012 select 'A0090', 200, 'MEMBER, SELF, NEIGHBOR) WITH VESTED INTEREST'
insert into HCPCS_2012 select 'A0100', 100, 'NON-EMERGENCY TRANSPORTATION; TAXI'
insert into HCPCS_2012 select 'A0110', 100, 'NON-EMERGENCY TRANSPORTATION AND BUS, INTRA OR INTER STATE CARRIER'
insert into HCPCS_2012 select 'A0120', 100, 'NON-EMERGENCY TRANSPORTATION: MINI-BUS, MOUNTAIN AREA TRANSPORTS, OR OTHER'
insert into HCPCS_2012 select 'A0120', 200, 'TRANSPORTATION SYSTEMS'

I am trying to concatenate the long description for an HCPCS and am trying to use a recursive function but i get an error message like below. When i create the function it give me this

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'dbo.udf_recursive'. The stored procedure will still be created.

which i understand because i am parsing the function when creating it but when compiling it it gives me this

Msg 217, Level 16, State 1, Procedure UDF_RECURSIVE, Line 11
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

My SEQ_NUM in group by HCPCS does not exceed 32 levels i.e) my seq num run from 100 to 1100 in multiples of 100

Can you tell me what ia m doing wrong?

Thanks

CREATE FUNCTION UDF_RECURSIVE ( @cid Varchar, @i int)
RETURNS VARCHAR(8000) AS BEGIN
DECLARE @r VARCHAR(8000), @l VARCHAR(8000)
SELECT @i = @i - 1, @r = LONG_DESCRIPTION + ' '
FROM HCPCS_2012 p1
WHERE HCPCS = @cid
AND @i = ( SELECT COUNT(*) FROM HCPCS_2012 p2
WHERE p2.HCPCS = p1.HCPCS
AND p2.SEQ_NUM <= p1.SEQ_NUM ) ;
IF @i > 0 BEGIN
EXEC @l = dbo.udf_recursive @cid, @i ;
SET @r = @l + @r ;
END
RETURN @r ;
END

SELECT HCPCS,
dbo.udf_recursive( HCPCS, COUNT(LONG_DESCRIPTION))
FROM HCPCS_2012
GROUP BY HCPCS;

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 14:59:23

Is this a test of using recursive functions - it's a complicated way of doing this.
http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

Try running it for one cid with a hard coded count.

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

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-11-01 : 15:31:11

I did it for one cid with 12 seq num and it gave the same error.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-11-01 : 15:53:59

i am using this instead of the recurive function in Sql 2005

SELECT DISTINCT HCPCS, DESCRIPTIONS
FROM HCPCS_2012 p1
CROSS APPLY ( SELECT LONG_DESCRIPTION + ' '
FROM HCPCS_2012 p2
WHERE p2.HCPCS = p1.HCPCS
ORDER BY SEQ_NUM
FOR XML PATH('') ) D ( DESCRIPTIONS )

and it works

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 17:27:46

Hence my question about why you are trying to do it this way.
Ah - v2000.

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-11-02 : 11:46:08

The way I did that in 2000 days was a simple scalar function to concatenate for one group (HCPCS). Then use it in a SELECT:

select HCPCS, dbo.fn_concatByHCPCS(HCPCS)
from (select distinct HCPCS from HCPCS_2012) d

The body of the function can be something like this:
declare @out
select @out = coalesce(@out + ',' + HCPCS, HCPCS) from HCPCS_2012 where HCPCS = @in
select @out

EDIT:
like this:


go
create function dbo.fn_concatbyHCPCS(@HCPCS  varchar(5))
returns varchar(1000)
as
begin
       declare @out varchar(1000)
       select @out = coalesce(@out + ', ' + Long_Description, Long_Description) 
       from HCPCS_2012 
       where HCPCS = @HCPCS
       order by Seq_Num
       return @out
end
go

select HCPCS, dbo.fn_concatByHCPCS(HCPCS) [LongDescription]
from (select distinct HCPCS from HCPCS_2012) d

OUTPUT:
HCPCS LongDescription
----- --------------------------------------------------------------------------------------------------------------------------------
A0021 AMBULANCE SERVICE, OUTSIDE STATE PER MILE, TRANSPORT (MEDICAID ONLY)
A0080 NON-EMERGENCY TRANSPORTATION, PER MILE - VEHICLE PROVIDED BY VOLUNTEER, (INDIVIDUAL OR ORGANIZATION), WITH NO VESTED INTEREST
A0090 NON-EMERGENCY TRANSPORTATION, PER MILE - VEHICLE PROVIDED BY INDIVIDUAL (FAMILY, MEMBER, SELF, NEIGHBOR) WITH VESTED INTEREST
A0100 NON-EMERGENCY TRANSPORTATION; TAXI
A0110 NON-EMERGENCY TRANSPORTATION AND BUS, INTRA OR INTER STATE CARRIER
A0120 NON-EMERGENCY TRANSPORTATION: MINI-BUS, MOUNTAIN AREA TRANSPORTS, OR OTHER, TRANSPORTATION SYSTEMS

EDIT2: added the "order by seq_num"

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources