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 2012 Forums
 Transact-SQL (2012)
 using a UDF in a view - need help

Author  Topic 

Movva
Starting Member

15 Posts

Posted - 2014-01-31 : 21:28:24
Hi,

I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.

Function:
-----------------------------------
ALTER FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),
@Delimiter varchar(100)
)
RETURNS @tblArray TABLE
(
DimensionID int IDENTITY(1,1), -- Array index
Dimension varchar(1000) -- Array Dimension contents
)
AS
BEGIN -- Local Variable Declarations -- ---------------------------
DECLARE @Index smallint,
@Start smallint,
@DelSize smallint
SET @DelSize = LEN(@Delimiter) -- Loop through source string and add elements to destination table array -- ----------------------------------------------------------------------
WHILE LEN(@DelimitedString) > 0
BEGIN
SET @Index = CHARINDEX(@Delimiter, @DelimitedString)
IF @Index = 0
BEGIN
INSERT INTO
@tblArray
(Dimension)
VALUES
(LTRIM(RTRIM(@DelimitedString)))
BREAK
END
ELSE
BEGIN
INSERT INTO
@tblArray
(Dimension)
VALUES
(LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))
SET @Start = @Index + @DelSize
SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)
END
END
RETURN
END




------------------------------------

View:
------------------------------------
CREATE VIEW [dbo].[ACLEDGERACCOUNTSANDFINANCIALDIMENSIONS]
AS
SELECT T1.TEXT AS TEXT,T1.LEDGERACCOUNT AS LEDGERACCOUNT,
T1.TRANSACTIONCURRENCYAMOUNT AS TRANSACTIONCURRENCYAMOUNT,
T1.ACCOUNTINGCURRENCYAMOUNT AS ACCOUNTINGCURRENCYAMOUNT,
T1.RECID AS RECID,T2.ACCOUNTINGDATE AS ACCOUNTINGDATE,
T2.SUBLEDGERVOUCHER AS SUBLEDGERVOUCHER,T2.JOURNALNUMBER AS JOURNALNUMBER,
T4.MAINACCOUNTID AS MAINACCOUNTID,T4.NAME AS ACCOUNTNAME,
(CAST ((SELECT DIMENSION FROM Dynamicsax.DBO.FUNC_SPLIT(T1.LEDGERACCOUNT, '-')
WHERE DIMENSIONID=4) AS NVARCHAR(100))) AS AREAOFLAW
FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN GENERALJOURNALENTRY T2 CROSS JOIN
DIMENSIONATTRIBUTEVALUECOMBINATION T3
LEFT OUTER JOIN MAINACCOUNT T4 ON (T3.MAINACCOUNT=T4.RECID)
WHERE (T1.GENERALJOURNALENTRY=T2.RECID) AND (T1.LEDGERDIMENSION=T3.RECID)
GO
------------------------------------

-----------------------------------
Here is the sample that works just fine and returns the temp table
Declare @string varchar(max);
set @string = '90032-GA-005-0511-001A';

select * from dbo.func_split(@string,'-');

-----------------------------------

Not sure what I am missing in the above view why it doesn't split the string. Can some one please help me what I am missing/doing wrong in the above view.

Thanks in advance.

Dev
   

- Advertisement -