SQl: Update the table using user defined functions

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi All,

How do I update the each coulmn values using using user defined functions , I tried with cursor but its taking long time to execute and I believe that this can be done using simple query but I am unable to come up with solution.

//table data

Record_Number TransactionTime Acktime
1 16/07/2014 Null
2 14/07/2014 Null
3 05/07/2014 Null

Functionality of the udf_getacktime : It takes the Record_number and performs some condition operation on different tables and get the acktime and resturns the same.

My requirement: Get the acktime for each record no and update the time for the record

The code is below:

tbltemp(Record_Number,TransactionTime,acktime=null) -- Acktime initially null

udf_getAckTime(record_Number) --Function takes the RecordNo and returns acktime

My update should be as below:
Update tbltemp
set acktime =udf_getAckTime(record_Number)
Where record_number should be the record no which I am sending as a parameter.

Can any one help me if you have any approach.

Regards
Prashant P

Posted 15-Jul-14 19:21pm

Prashant Bangaluru

Updated 15-Jul-14 21:19pm

v2

Add a Solution

Comments

Maciej Los 16-Jul-14 1:50am

Provide sample data and shortly describe what udf_getAckTime() does.

2 solutions

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)

Atyant Srivastava · Answer 1 · 2014-07-15T19:49:00

Solution 1

You can do this through CROSS APPLY, please look at the sample below:

SQL

UPDATE A SET A.acktime=B.recordvalue FROM tbltemp A
CROSS APPLY 
( 
   SELECT udf_getAckTime(A.acktime) AS recordvalue
) B
WHERE record_number='your Record Number'

Although this code is only for your reference and you can optimize it more.

Posted 15-Jul-14 19:49pm

Atyant Srivastava

Comments

Prashant Bangaluru 16-Jul-14 3:11am

Hi Sri, We have to perform all the operations on a single table. I have edited my question . Thanks for your time.

Atyant Srivastava 16-Jul-14 4:48am

hi, but here also i am using a single table only, there is no second table involved.
CROSS APPLY is used to join table and function. Here alias B is representing your function.

Maciej Los · Answer 2 · 2014-07-15T19:56:00

For that functionality you can use: While[^] loop, While loop + cursor[^] and the last one is Common Table Expressions[^].

I'm almost sure you can use simple UPDATE + SELECT query:

SQL

UPDATE t1 SET t1.DestinationField = t2.SourceField
FROM DestinationTable AS t1 INNER JOIN SourceTable AS t2 ON t1.Key = t2.ForeignKey

where Key - in your case - would be Record_Number.

[EDIT]

SQL

UPDATE t1 SET t1.AckTime = udf_GetAckTime(t2.AckTime)
FROM YourTableName AS t1 INNER JOIN YourTableName AS t2 ON t1.Record_Number = t2.Record_Number

[/EDIT]