SPLIT FUNCTION NOT WORKING CORRECTLY

Question

SPLIT FUNCTION NOT WORKING CORRECTLY

ian_dunlop2000

SSC-Addicted

Points: 430
More actions
April 13, 2008 at 10:20 am

#205700

HI All
Wonder if anyone can help me with a funtion I have that does not work completly.
THE FUNCTION..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplit]
(
@List nvarchar(max),
@SplitOn nvarchar(3)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(3)
)
AS
BEGIN
WHILE (Charindex(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
SET @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = ltrim(rtrim(@List))
RETURN
END
It reads a text list in a memo field which contains codes (max 3 characters) such as S01 ,F03 ,R01 ,T12...so on
what it does is in no particular order is reads so many miss one and continue reading - so on so in a list of 20 it may miss out 4 codes
the SPROC.....
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspBroker]
@SFN varchar(100),
@CONAME varchar(100),
@DEALREF varchar(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT
o.AGY_NEW_CHANNEL,
o.AGY_NEW_REGION,
o.AGY_NEW_BUS_MGR,
o.AGY_COMPANY_NAME,
o.AGY_SALESFORCE_CDE,
o.AGY_SALESFORCE_NAME,
o.PRD_BUS_LINE_CD,
o.PRD_BUS_LINE_TP,
o.TUS_KEY,
o.TUS_TRAD_UNIT_DS,
o.DAT_YEAR_MONTH,
o.GWP,
o.GEP,
o.OCCURRED_COST,
o.MARGINED_COST,
o.NACS_IND,
o.NBKR_IND,
o.TAM_IND,
o.T2_IND,
o.T3_IND,
o.AGY_ACCOUNT_REF,
o.TARG_GWP,
o.TARG_GWP_GOLD,
o.TYPE_NAME,
o.FIN_NAME,
o.FULL_LAST,
o.Deal_Ref,
o.CURR_YTD,
o.PREV_YTD,
o.PREV_MAT,
o.CURR_MAT,
o.Deal_Type_CDE,
o.Fianace_Type_CDE,
o.Code1,
o.Code2_Groups,
o.DEALS_LGTH,
o.START_DATE,
o.END_DATE,
g.Business_Lines
FROM TBL_OUTPUT o
JOIN GOLD_0308 g ON o.Deal_Ref = g.Deal_Ref
WHERE
(o.AGY_SALESFORCE_NAME = @SFN)
AND
(o.AGY_COMPANY_NAME = @CONAME)
AND
(o.Deal_Ref = @DEALREF)
AND
o.PRD_BUS_LINE_CD IN (SELECT CONVERT (Nvarchar(3),Value) FROM dbo.fnSplit(g.Business_Lines, ' ,'))
ORDER BY o.PRD_BUS_LINE_CD
END
HOPING THE ANSWER TO THIS IS OBVIOUS TO THE MORE EXPERIENCED

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

The spit function works just fine and doesn't miss a thing. If, however, you don't have the data in the table to match, an INNER JOIN may give the illusion of the function not working... you may have to do an outer join on the function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)