Solved

SQL Function not working properly

Posted on 2015-01-05
2
108 Views
Last Modified: 2015-01-05
I have the following function that seems to work outside of the fact that if my list gets to long it doesn't seem to pull the propertyid's after a certain point.  Can anyone tell my why this would work 'HC,SM,WG,KG,CL,NH,CM,CH,CW,GO,KA,RL,SO,OF,WP,CG,PP,BF,CS' but this will not
'HC,SM,WG,KG,CL,NH,CM,CH,CW,GO,KA,RL,SO,OF,WP,CG,PP,BF,CS,DT,HT,HP,L1,L2,BW,B1,B2,BR,WA,WN,WT,SP,ME,JP,PV'

Here is the code and
ALTER Function [dbo].[fn_Txt_Split]( 
    @sInputList varchar(max) -- List of delimited items 
  , @Delimiter char(1) = ',' -- delimiter that separates items 
) 
RETURNS @list table (Item varchar(max)) 
as begin 
DECLARE @Item Varchar(max) 
  
  

WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0 
BEGIN 
SELECT 
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0 
)-1))), 
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList)))) 
  
IF LEN(@Item) > 0 
INSERT INTO @List SELECT @Item 
  
END 

  
IF LEN(@sInputList) > 0 
INSERT INTO @List SELECT @sInputList -- Put the last item in 
  
return 
END 

Open in new window


thanks in advance for the help
0
Comment
Question by:mgmhicks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40531799
Worked fine for me.  Make sure the variable you're putting the values back into is correctly coded.

Btw, you should do a Google search for 8K Splitter, edited by Jeff Moden, which is an extremely efficient, fully-tested split function.
0
 

Author Closing Comment

by:mgmhicks
ID: 40531865
You were correct it was the procedure that called the function that had the problem.  Thank you
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server client app 3 35
SQL Select Query help 1 38
Linked Server - SP with Param to VIew 7 25
SQL syntax question 6 45
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question