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