Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem when using a User Defined Function in a SET loop Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, December 22, 2012 4:35 PM
Points: 12, Visits: 27
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.

.
Post #1386056
Posted Sunday, November 18, 2012 11:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
For starters, your UPDATE code isn't updating anything in the table.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386081
Posted Monday, November 19, 2012 12:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
Your @title is null (not set), hence function is returning NULL. Why quirky update? It is probably the last thing I would do.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1386179
Posted Monday, November 19, 2012 6:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
Vedran Kesegic (11/19/2012)
Your @title is null (not set), hence function is returning NULL. Why quirky update? It is probably the last thing I would do.


Look again... it's not a quirky update because the code doesn't actually update any data.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386357
Posted Monday, November 19, 2012 6:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:27 AM
Points: 369, Visits: 1,214
It updates (sets) variables to null.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1386361
Posted Monday, November 19, 2012 6:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
Vedran Kesegic (11/19/2012)
It updates (sets) variables to null.


Understood but it's still not a quirky update. Not even close to being one.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1386364
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse