UPDATE in UDF

Is it possible to run an UPDATE statement in a user defined function?  I am 
creating a UDF that determines the amount of a payroll deduction.  Some 
deductions have a balance (total) to deduct over time.  If a deduction is a 
balance deduction then I want to be able to reduce the balance by the amount 
of the deduction.  For example, a deduction has an amount of $10 and a 
balance of $100.  When I take this deduction I need the balance to be reduced 
to $90.  Below is the table schema that I am using. Thanks for any help on 
this or if someone can point out a different way, I would appreciate it.  
Thanks.

CREATE TABLE [dbo].[WorkerDeductions](
	[WorkerDeductionID] [int] IDENTITY(1,1) NOT NULL,
	[PeopleLinkID] [int] NOT NULL,
	[DedCode] [int] NOT NULL,
	[VendorID] [int] NOT NULL,
	[DedAmt] [smallmoney] NULL,
	[DedBalance] [smallmoney] NULL,
	[DedPercent] [smallmoney] NULL,
	[ReimbRate] [smallmoney] NULL,
	[DedNote] [nvarchar](50) NULL,
	[DedStart] [date] NULL,
	[DedEnd] [date] NULL
-- 
David
0
Utf
6/2/2010 5:10:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
719 Views

Similar Articles

[PageSpeed] 52

If you are updating a row when  deduction is made -- I would use a trigger to 
update your table based on the action taken (the deduction).  If you are 
adding a row to your table to show the history of the account, then you could 
use a udf to calculate the new balance.  I think you could still also use a 
trigger to update the new record.

Rich

"DavidC" wrote:

> Is it possible to run an UPDATE statement in a user defined function?  I am 
> creating a UDF that determines the amount of a payroll deduction.  Some 
> deductions have a balance (total) to deduct over time.  If a deduction is a 
> balance deduction then I want to be able to reduce the balance by the amount 
> of the deduction.  For example, a deduction has an amount of $10 and a 
> balance of $100.  When I take this deduction I need the balance to be reduced 
> to $90.  Below is the table schema that I am using. Thanks for any help on 
> this or if someone can point out a different way, I would appreciate it.  
> Thanks.
> 
> CREATE TABLE [dbo].[WorkerDeductions](
> 	[WorkerDeductionID] [int] IDENTITY(1,1) NOT NULL,
> 	[PeopleLinkID] [int] NOT NULL,
> 	[DedCode] [int] NOT NULL,
> 	[VendorID] [int] NOT NULL,
> 	[DedAmt] [smallmoney] NULL,
> 	[DedBalance] [smallmoney] NULL,
> 	[DedPercent] [smallmoney] NULL,
> 	[ReimbRate] [smallmoney] NULL,
> 	[DedNote] [nvarchar](50) NULL,
> 	[DedStart] [date] NULL,
> 	[DedEnd] [date] NULL
> -- 
> David
0
Utf
6/2/2010 5:29:36 PM
"Rich" wrote:

> If you are updating a row when  deduction is made -- I would use a trigger to 
> update your table based on the action taken (the deduction).  If you are 
> adding a row to your table to show the history of the account, then you could 
> use a udf to calculate the new balance.  I think you could still also use a 
> trigger to update the new record.
> 
> Rich
> 
> "DavidC" wrote:
> 
> > Is it possible to run an UPDATE statement in a user defined function?  I am 
> > creating a UDF that determines the amount of a payroll deduction.  Some 
> > deductions have a balance (total) to deduct over time.  If a deduction is a 
> > balance deduction then I want to be able to reduce the balance by the amount 
> > of the deduction.  For example, a deduction has an amount of $10 and a 
> > balance of $100.  When I take this deduction I need the balance to be reduced 
> > to $90.  Below is the table schema that I am using. Thanks for any help on 
> > this or if someone can point out a different way, I would appreciate it.  
> > Thanks.
> > 
> > CREATE TABLE [dbo].[WorkerDeductions](
> > 	[WorkerDeductionID] [int] IDENTITY(1,1) NOT NULL,
> > 	[PeopleLinkID] [int] NOT NULL,
> > 	[DedCode] [int] NOT NULL,
> > 	[VendorID] [int] NOT NULL,
> > 	[DedAmt] [smallmoney] NULL,
> > 	[DedBalance] [smallmoney] NULL,
> > 	[DedPercent] [smallmoney] NULL,
> > 	[ReimbRate] [smallmoney] NULL,
> > 	[DedNote] [nvarchar](50) NULL,
> > 	[DedStart] [date] NULL,
> > 	[DedEnd] [date] NULL
> > -- 
> > David

The WorkerDeductions table is only updated after I have created a record in 
a different record that is linked to the paycheck. The trigger idea sounds 
good but not sure how to do that as only those deductions with a balance need 
to be updated.

-- 
David


0
Utf
6/3/2010 11:11:53 PM
Reply:

Similar Artilces:

Is it possible with Linq to update a dataset?
Hi, I have this working code which update a column: For Each rigaFF As DataRow In Me.dtSizeA.Rows For Each rigaTemp As DataRow In Me.dtSizeB.Select(String.Concat("Name = ", CStr(rigaFF(0)))) rigaTemp(18) = rigaFF(3) Next Next Is it possible to do the same with Linq? Thanks in advance for your help!!! Cheers, BlackSuna AFAIK No. if you want to use Linq for SQL Server (not other ones) have a look to Linq to SQL or Linq to EF It is easy with Linq to SQL, just add a Linq to SQL item and go than to server explorer to select the tables like in ...

Recommended sequence of DST update actions.
The Support Article: Preparing for Daylight Saving Time changes in 2007 http://support.microsoft.com/default.aspx/gp/dst_topissues States that you should: Apply Updates to the Windows Servers (931836) Apply Updates to the Individaul Workstations (931836) Apply the Exchagne Server DST Update. (926666) Then Run the Exchange Calendar Update Tool (930879) but when you look at the KB930879 How to address daylight saving time by using the Exchange Calendar Update Tool http://support.microsoft.com/kb/930879 It says you should apply the Exchagne DST Update (926666) after you run the Exchange Cal...

OL 2003 with latest Office Update for Junk Mail #2
Yesterday I downloaded the latest automatic update for Outlook 2003 pertaining to Junk Mail filtering. Prior to the update, using Cached Exchange Mode and a HIGH junk Mail filter setting, OL caught virtually 100% of the spam. Now, it catches significantly less. What gives MS? ...

Use that correction update from the Microsoft
--fobjkuxj Content-Type: multipart/related; boundary="mifxvowfcttgix"; type="multipart/alternative" --mifxvowfcttgix Content-Type: multipart/alternative; boundary="nwfkfltyzlsv" --nwfkfltyzlsv Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protec...

PDK Template
PDK Template tables (PDK00300 & PDK00301) hold employee specific data (such as Department from UPR00100). These fields should update when the source is updated. For example, I have a report that uses the PADepartment field from PA30101. This allows me to report historical time for the correct department for the employee at that time. However, if they use a template to start their timesheet in Business Portal, the department field does not currently get updated when I change their department in Employee Maintenance. Therefore, all subsequent time for existing projects on their t...

Office 12.2.4 update crashes the system
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I returned from vacation to a computer that had worked fine and downloaded this update. For some reason the update wouldn't install. I quit the update and now my computer has all sorts of problems: disks won't eject, the computer won't sleep, logging out and restarting don't work, several applications, including System Preferences don't respond. I really, really, really don't want to erase the hard drive and begin again. Any suggestions, aside from slowly roasting the writer of this last ...

setup online update for investment account
I start to setup online update but when it asks for user id and password ihe message returned is that the id or password were invalid, yet when I go to the web page of the firm that has the investment and use the same id and password I have no trouble logging on. the investment firm is amerprise. any help or pointers to any info that may help will be appreciated Ed Christie In microsoft.public.money, Ed Christie wrote: > >I start to setup online update but when it asks for user id and >password ihe message returned is that the id or password were invalid, >yet when I go to the...

Automatically update filtered list across worksheets
How can I have the results of a filtered list automatically update when a change is made to the source list which is on another worksheet I have one worksheet which is a list of events sorted by date. On another worksheet I want to show all the events that occur in a particular month. I can filter the list from the one sheet to another without a problem, but any changes made to the first sheet do not automatically appear on the second. If I add a new event to the main sheet, I would like it to automatically appear on the second (if it fullfills the criteria). How can this be done You could...

Sales Configurator and MOP Updating
My client is an active user of sales configurator module. Their process starts with a sales quotation. The quotation results in a configured bom used to create a manufacturing order (quote/estimate status). Quite often, customers call to activate their quotation, many times the quantity or dates used in sales quotation change as the sales process progresses. Currently, the system does not allow for 'dynamic' linking between an SOP Document and the MOP document linked to it via Sales Configurator. Their suggestion would be to make the two documents be dynamically linked - any c...

Mac OS X Security Update 2007-005 Universal
Security Update 2007-005 causes Microsoft Office to fail Has any body else seen the same effects as me - after the auto-update of Mac OS Update 2007-005, none of my Microsoft Office 2004 applications can save or PDF any of my files. Office simply crashes. I'm trying to eliminate things - if other people are seeing the same issue, then perhaps i'm not going mad Mike Hi Mike - The Security Update is an Apple OS X release of course, but thanks for making us aware of your experience with it - sorry it seems to be an unfortunate one :( A brief tour of the Apple Discussions Forum indi...

GP10 SP3 UPDATE
We are running SP3 and ran into problems with payroll and direct deposit. We are re-installing a later version of SP3 and now when we attempt to log into a company the following error shows up: Anavailable update may be required for your computer but the update process could not verify information, then GP kicks us out. Any suggestions? -- Thanks for your help Jayne JayneShoe, Find the solution in my blog post below: http://mohdaoud.blogspot.com/2008/12/available-update-may-be-required-for.html Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great ...

Take a look at this update from the M$
--avjktrbvaxsvc Content-Type: multipart/related; boundary="huolykslbclwktmal"; type="multipart/alternative" --huolykslbclwktmal Content-Type: multipart/alternative; boundary="bvfstiikntomcsuhd" --bvfstiikntomcsuhd Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. ...

Update entries in a worksheet, over the internet
Hi, I have a single excel worksheet. I want to update this worksheet over the web. I have some friends at a remote location who would like to access this. It should take in entries and add up some totals, that's it. Is there a way to do this in Excel? I have a webserver and can work with some CGI-scripts. Thanks, Prem. "Prem Rachakonda" <prem_racha@hotmail.com> wrote in message news:2628858f.0311170835.6f157da7@posting.google.com... > Hi, > I have a single excel worksheet. I want to update this worksheet > over the web. I have some friends at a remote locati...

Office 2008 will not update or run in other than safe mode in OS 10.6
Version: 2008 Processor: Intel OS 10.6 Snow Leopard 2 GHz intel duo 2 GB 677 MHz Ram Office 2008 12.2.0 When I run any office module it does not load, when I run Word I just get "optimizing fonts" message and I have to force quit (application not responding). It runs in safe mode but when I try to do the update, even in safe mode, to 12.2.1 it says it says that "a version of the software required to install this update was not found on this volume". I did a reinstall from the CD in safe mode (quit all programs, select remove Office, restart after install) and then tried t...

Two Office 03 Security updates failed
I had a trial Office 07 that I uninstalled using the removal tool. Nevertheless, Update installed several Office 07 updates but choked on these. I also have Office 02 Developer installed. More info: Running XP on an HP laptop. These updates are old. Wouldn't I have already had them installed? I don't see them in Control Panel, Add or Remove Programs. "dw0rd" wrote: > I had a trial Office 07 that I uninstalled using the removal tool. > Nevertheless, Update installed several Office 07 updates but choked on > these. > > I also have ...

Update Access 2000
Have Access 2000. Tried to open a .mdb file - pop-up tells me I have to update - file created by a newer version of Access - what files do I need? Do I have to take the whole Office update? Exactly where do I find them? Greenhorn here. Help. Thanks. Hi Ken, You'd need to upgrade to Access 2002 or Access 2003 - and preferably upgrade Office as a whole. This involves purchasing the software from a retailer (or getting your corporate IT department to supply it). It would be simpler and cheaper (if it's possible) to ask whoever created the mdb to use their version of Access (2002, p...

The on the server published free/booked-information could not be updated. The task could not be executed OL 2000
Hello, outlook 2000 (german) running with windows xp (german) gives the following error message window when quitting after adding a date in my personal folder calender from a other user: "The on the server published free/booked-information could not be updated. The task could not be executed" Please help me fix this message. Thank you. regards, Dirk Lehmann Well, the short version is that for whatever reason, your outlook can't connect to your free/busy server to update the information. What causes the error depends on your network configuration. -- Diane Poremsky ...

Change outline numbering, hanging indent not updated
I followed http://www.shaunakelly.com/word/numbering/OutlineNumbering.html to define an outline numbering scheme. For each additional outline level, I added 0.12" of extra indentation for the text of the heading. The tabstop settings are properly updated in each Heading style, but the hanging indents is not. I manually modified the hanging indents to match the tab settings. Is there a more intelligent way to have the hanging indents update automagically? When you apply outline numbering to paragraph styles, the indents and tabs must be set in the Numbering dialog, not in the...

Adding/updating totals from one worksheet to another
I have created several worksheets within one file. I have a summary worksheet and multiple detail worksheets. When I update the detail worksheet I want the totals to be updated in the summary worksheet. How do I do this? Thanks for your help. Ila Ila, something like this in your detail sheet ='Detail Sheet 1'!A1, and easy way to do this is to click in your summary sheet where you want the total from one of your detail sheets and put = then click on the detail sheet and click on the cell you want and press enter -- Paul B Always backup your data before trying something new ...

Free/Busy information not updating as per schedule
Hi Outlook 2003 fully updated. I've configured clients to update their free/busy information to a network share and they are configured to update every 15 minutes....but it does not work. Clients have to manually click on TOOLS > SNED/RECEIVE > FREE/BUSY INFORMATION for it to be updated. Any ideas. TIA KIWI ...

Security Update for SQL Server 2005 Service Pack 2 (KB970895) 11-27-09
It appears that numerous people have had the same problem that I had where this update failed at every attempt to install it. I tried many of the suggestions in the various newsgroups without success, then today I looked at the SQL services under Control Panel/Administrative Tools/Services and saw that SQLExpress and SQL Active Directory Helper were not started, although they were set to Automatic. I tried starting them manually but neither would start. I then went to Properties, Log On and changed the log on to "Local System Account". I then tried starting the two s...

FWD: Watch that corrective update
--bzswioqrorlfkaadw Content-Type: multipart/related; boundary="gmmanxwlmsdr"; type="multipart/alternative" --gmmanxwlmsdr Content-Type: multipart/alternative; boundary="giijvxqn" --giijvxqn Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to co...

How to update a document
I have a large document in which I'd like to have the user enter some values for several static names, press a button to have Word automatically update all references to those static names throughout the document. The table looks like this and there are more name/value pairs. The user will update the value for each name as required. Name Value Current release 1.5.4.7 Upgrade release 1.5.5.1 UI Server name Sterling DB server name Sterling Further down in the document are references to these Names such as the following. For us the Names are Bold ...

Updates download but fail to install repeatedly
It seems there are a lot of people who are having problems with updates downloading but not installing. I now have this problem on two different computers in my house on one, the list is quite long now: Update for Microsoft Office Outlook 2003 Junk Email Filter (KB974771) Security Update for Microsoft Office 2003 (KB972580) Update for Microsoft Office 2003 (KB978551) Security Update for Microsoft Office Word 2003 (KB973443) Security Update for Microsoft Office Excel 2003 (KB973475) Security Update for Microsoft Office 2003 (KB974554) Security Update for Microsoft Office 2003 (KB9...

Update Cell in Multiple Sheets
My apologies if I have done this incorrectly. After closing off a previous post I added to this question. Is there someway to select and update Multiple Worksheets in a Macro, I have tried a couple of ways below I have included my latest code. Sheets(Array("Regional Freight-QNRFA", "Int Retail-QNIMR", "Int Wholesale-QNIMW", _ "Passenger-PS", "Network-NW", "Infrastructure-IS", "Workshop-WS", "COMB IS & WS")).Select Dim d As Date d = InputBox("Enter the desired date.&qu...