SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Using a User Defined Function in a SET loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

henryvuong
Starting Member

USA
3 Posts

Posted - 11/18/2012 :  11:27:39  Show Profile  Reply with Quote
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.


.

Edited by - henryvuong on 11/18/2012 11:28:57

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/18/2012 :  17:36:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000