How do I call a UDF get the returned values and update the table

Question

0.00/5 (No votes)

See more:

Hello,

I have a UDF([Name_Parser]) that accepts a name
('Mrs. Thurston Howell, IV' and returns the following

original: Mrs. Thurston Howell, IV
prefix: Mrs
firstName: Thurston
middleName:
lastName: Howell
suffix: IV

So the UDF is working fine.

---------------------------------------------------

DECLARE @TNAME  varchar(50)
    SET @TNAME = 'Mrs. Thurston Howell, IV'

SELECT * INTO #TempVariable1 
FROM [dbo].[Name_Parser](@TNAME)


SELECT  
   original ( Full Name Field )
 , prefix
 , firstName
 , middleName
 , lastName
 , suffix
  FROM #TempVariable1

---------------------------------------------------

What I need to do is Perform an update on the User Table and take the Name data and pass it to the UDF, get the results and update the different fields and complete the update.

Any ideas on how this can be done?

What I have tried:

Everything that I can think of.

UPDATE dbo.USER
SET First_Name = firstName
SET Last_Name  = lastName

I just do not know how to make this work.
Any constructive help would be greatly appreciated.

Posted 7-Apr-24 14:21pm

markmoss

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2024-04-08T00:05:00

Solution 1

Something like this should work:

SQL

UPDATE
    U
SET
    First_Name = P.firstName,
    Last_Name = P.lastName
FROM
    dbo.[USER] As U
    CROSS APPLY dbo.Name_Parser(U.Full_Name) As P
;

However, you should probably read Falsehoods Programmers Believe About Names[^] first. :)

Posted 8-Apr-24 0:05am

Richard Deeming

Comments

[no name] 9-Apr-24 2:23am

Thank you so much for this response Richard, It worked just the way you coded it.

Many Many Many Thanks

Mark Moss

Maciej Los 9-Apr-24 11:41am

+5!