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 2008 Forums
 Other SQL Server 2008 Topics
 Using a User Defined Function in a SET loop

Author  Topic 

henryvuong
Starting Member

8 Posts

Posted - 2012-11-18 : 11:27:39
I am using SQL Server 2008 R2. I created a User Defined Function like this:


CREATE FUNCTION [dbo].[Custom_StringToTableWithID]
(
@string VARCHAR(MAX),
@delimiter CHAR(1)
)
--The return table has a column with auto-increment primary key and a column with text
--The text column is the result of the split string from the input
RETURNS @output TABLE( ID int identity primary key, Data VARCHAR(MAX))

BEGIN

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (data)
--Stripped off any new line character, carriage return character, leading and trailing spaces in the insert value
--Each new line and carrage return characters is replaced by a blank space
VALUES (LTRIM(RTRIM(REPLACE(REPLACE(SUBSTRING(@string, @start, @end - @start),CHAR(10), ' '), CHAR(13), ' '))))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN

END


I have a table named "CUSTOM_test" with two columns:

ID Title
Item1 Lord of the Rings
Item2 The Hobbits
Item3 Dark Knight Rises

When I write code like this, the value of @word is "Lord":


DECLARE @title nvarchar(100)
SET @title = (SELECT Title FROM CUSTOM_test WHERE ID = 'Item1')
DECLARE @word nvarchar(20)
SET @word = (SELECT Data FROM Custom_StringToTableWithID(@title, ' ') WHERE ID = 1)



But when I write code like this, the value of @word is NULL:


DECLARE @title nvarchar(100)
DECLARE @word nvarchar(20)
UPDATE CUSTOM_test
SET
@title = Title,
@word = (SELECT Data FROM Custom_StringToTableWithID(@title, ' ') WHERE ID = 1)
WHERE ID = 'Item1'


The later code is just a simplified version. I actually need to loop through the whole table and there's more code in that, but it cannot work as long as @word is null. Can someone give me an explanation why @word is null? Thanks.


.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-18 : 17:36:20
Are you trying to update the table or are you trying get all the words in the table? If you are trying to get the words in the table, use it like this:
SELECT
f.*
FROM
CUSTOM_Test a
CROSS APPLY Custom_StringToTableWithID(a.title,' ') f
If you are trying to update the table, you can make the code above into a subquery and update using that.

BTW, performance of loop based string splitters can be very poor. There are some excellent string splitters that perform well available, for example, see Jeff Moden's article here (Fig. 21) http://www.sqlservercentral.com/articles/Tally+Table/72993/
Go to Top of Page
   

- Advertisement -