Convert an Access 2003 Function to a MS SQL 2005 Function

I am creating a simple Stock DB.
Could you please show me how I can move this Function from Front End Access to the Back End SQL Server where I know it will run much quicker.  I would also like to create a trigger that runs this function after an Insert or Delete event as well.  This will enable my StockItems table to always be up-to-date with the current stock level.
=========
Function CalcRecievedItemsRemainingStockItem(FncStockItemID)
' This will calculate the quantity remaining for a single Stock Item ID
On Error GoTo Err_CalcRecievedItemsRemaining

Dim db As ADODB.Connection
Dim strsql As String

Set db = CurrentProject.Connection

strsql = "UPDATE tblReceivedItem LEFT JOIN vewTotalDispatchedForEachItemReceived ON tblReceivedItem.ReceivedItemID = vewTotalDispatchedForEachItemReceived.ReceivedItemID SET tblReceivedItem.StockRemaining = IIf(IsNull(SumOfQuantity),Quantity,Quantity-SumOfQuantity) WHERE (((tblReceivedItem.StockItemID)=" & FncStockItemID & "))"
Debug.Print strsql
db.Execute strsql

Set db = Nothing

Exit_CalcRecievedItemsRemaining:
    Exit Function

Err_CalcRecievedItemsRemaining:
    MsgBox Err.Description & " " & Err.Number
    Resume Next
   
End Function
===========
CABHughAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardCorrieCommented:
by function i suppose you mean stored procedure;  An SQL Function cannot be used to update data;
I am assuming that FncStockItemID is an int  - you have to declare the type of variable in SQL. ( a bit naughty of you not to do son in your access module<g>)

create procedure CalcRecievedItemsRemainingStockItem(@FncStockItemID int)
as
UPDATE   tblReceivedItem
SET
StockRemaining = case when SumOfQuantity is null then Quantity else Quantity-SumOfQuantity end
From
tblReceivedItem LEFT JOIN vewTotalDispatchedForEachItemReceived
ON tblReceivedItem.ReceivedItemID = vewTotalDispatchedForEachItemReceived.ReceivedItemID
WHERE
tblReceivedItem.StockItemID = @FncStockItemID
go
0
CABHughAuthor Commented:
Hi Richard
Yes it's an int ! ;)

22:22 here in Portugal so I'll try this when I'm in the Office tomorrow morning.  Thanks for responding so promptly.

It looks like exactly what I need and a hell of a lot simpler than I expected to see!

How can I call this with an Insert or Delete Trigger (can I do and Update trigger as well?) to update the table automatically without action from the Front end?

Hugh
0
CABHughAuthor Commented:
Hi Richard
Tried this and it does just what I need - Thanks.
2 more questions (so I've tripled the points!):
1 - How do I set the triggers in SQL (Update, Delete, Insert) ?
2 - How do I call these stored procedures from Access front end manually if I choose ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RichardCorrieCommented:
1) you can call this "in a trigger" but need to be careful; which table is being changed which will fire this code
you use the create trigger <<triggerName>> on <<TableName>>
for Insert| update| delete.  If you let me have the name of the table and the structure (or at least the relevant columns ) i wiull try to write the code...

2) to call the proc from access front end, depends on the type of front end u have;  If it is a "normal" (.mdb) access with linked odbc tables u can use a passthru query; if u have created a project (.adp) u can use ADO to call the procedure (look in the help file about calling procedures) but code could look something like

dim cmd as new ado.command
dim prm as ado.parameter
set cmd.connection = currentproject.connection
set prm = cmd.createparameter("@FncStockItemID",sqldb.int) '<-- you will have to use intellisense to get this correct
prm.value = FncStockItemID
cmd.parameters.add(prm)
cmd.execute

/Richard

0
CABHughAuthor Commented:
Sorry for the delay in replying.

I have used you example to create several Stored Procedures and they work well.  As an example of how I wish to use these I will use a slightly different one to previously.  This procedure calculates the total received items and dispatched items to calculate the total remaining (CurStockQty).

Stored Procedure Modify shows this:
ALTER procedure [dbo].[UpdateStockLevelSingle](@StockID int)
as
UPDATE    tblStockItem
SET           TotalReceived = isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0),
                 TotalDispatched = isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0),
                 CurStockQty=isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty - vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0)
FROM         tblStockItem INNER JOIN
                      vewTotalReceivedAndDispatchedPerStockItem ON tblStockItem.StockItemID = vewTotalReceivedAndDispatchedPerStockItem.StockItemID
WHERE
vewTotalReceivedAndDispatchedPerStockItem.StockItemID = @StockID

I wish the trigger to run on the Insert, Update, Delete event in tblReceivedItems table.
====
I am using a MDB (MDE when issued to users later) and have looked at the pass-through but am now lost!  If I can see a working example I can usually figure it all out from that.
0
CABHughAuthor Commented:
Hi Richard

Know you're busy but any chance you'll have a moment to help further?  If not then I may need to post a new question.

Regards

Hugh
0
RichardCorrieCommented:
if u are using several sps and they are not going to be a source for a form or report u can use ADO in your code, see my example (2) above - you will have to add a reference to Microsoft Advanced Data Objects n.x to you code base.

/Richard
0
CABHughAuthor Commented:
Thanks for response and will try the ADO.

What I really want is for this recalculation to happen automatically on INsert, Delete, Update so the Stock levels are always up to date.  It strikes me that this is best achieved by using a trigger on the table.  I just don't know how to write the trigger that will run my SP.
0
RichardCorrieCommented:
I gues u have 3 tables here: tblStockItem; tblStockItemDispatched, tblStockItemRecieved?

u then need triggers on tblStockItemDispatched and tblStocklItemReceived to update tblStockItem:

Create trg_AddStockItem on tblStockItemReceived
for insert,Update
as
UPDATE    tblStockItem
SET           TotalReceived =TotalReceived  + I.ReceivedQty,
                 CurStockQty= CurStockQty + I.ReceivedQty
FROM        
tblStockItem  SI
INNER JOIN
inserted I
on
SI.StockID = I.stockID
go
Create trg_RemoveStockItem on tblStockItemDispatched
for insert,Update
as
UPDATE    tblStockItem
SET           TotalDispatched =TotalDispatched + I.DispatchedQty,
                 CurStockQty= CurStockQty - I.DispatchedQty
FROM        
tblStockItem  SI
INNER JOIN
inserted I
on
SI.StockID = I.stockID
go

/Richard

0
CABHughAuthor Commented:
That would work well but I want to be able to allow the users to change the quantity after they have inserted in case of typos etc.
Using this method each update would add to the stock so I would very quickly have a problem!

By using the SP that recalculates all recived and dispatched items I will always have an accurate calculation.  I'm not talking about thousands of movements so the numbers involved are quite managable.

The SPs already do the calculations and update the tables I require.

Question - How do I create the Triger that will run a SP on Inset, Update or Delete ?

Getting quite desperate now so have increased points to 500.

Thanks

Hugh
0
RichardCorrieCommented:
if the user can change the values (TotalDispatched, TotalReceived, CurStockQty) in tblStockItem then the triggers i have wriiten will still work.

or
Create trg_AddStockItem on tblStockItemReceived
for insert,Update

UPDATE    tblStockItem
SET           TotalReceived = isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0),
                 TotalDispatched = isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0),
                 CurStockQty=isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty - vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0)
FROM        
tblStockItem
INNER JOIN
vewTotalReceivedAndDispatchedPerStockItem
ON
tblStockItem.StockItemID = vewTotalReceivedAndDispatchedPerStockItem.StockItemID
inner join
Inserted I
vewTotalReceivedAndDispatchedPerStockItem.StockItemID = I.StockItemID
go
/Richard

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CABHughAuthor Commented:
This looks fine - Thanks

How would I write this trigger if I just wanted to run an existing SP?

Hugh
0
RichardCorrieCommented:
calling an SP from a trigger is not very effficient as u have to use a cursor; it is much better to use the code from SP as basis for trigger; however your wish is my command<g>:

Create trg_AddStockItem on tblStockItemReceived
for insert,Update
as
declare @StockID int
declre crsStock as cursor local fast_forward for
select StockItemID from inserted

open crsStock
fetch next from crsStock into @StockID
while @@Fetch_status = 0
begin
exec CalcRecievedItemsRemainingStockItem @StockID
fetch next from crsStock into @StockID
end
close crsStock
deallocate crsStock
go
/Richard
0
CABHughAuthor Commented:
Many thanks Richard

I did have to add the 'AS' and 'ON' to get it to work but now fine.

Thank you for giving me your time.

Hugh
0
CABHughAuthor Commented:
Sorry to trouble you again but the Delete triger does not work!
Any Idea why?

create trigger [dbo].[trg_UpdateStockLevelsDelete] on [dbo].[tblReceivedItem]
for delete
as
UPDATE    tblStockItem
SET           TotalReceived = isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0),
                 TotalDispatched = isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0),
                 CurStockQty=isnull(vewTotalReceivedAndDispatchedPerStockItem.ReceivedQty,0) - isnull(vewTotalReceivedAndDispatchedPerStockItem.DispatchedQty,0)
FROM        
tblStockItem
INNER JOIN
vewTotalReceivedAndDispatchedPerStockItem
ON
tblStockItem.StockItemID = vewTotalReceivedAndDispatchedPerStockItem.StockItemID
inner join
Inserted I
on
vewTotalReceivedAndDispatchedPerStockItem.StockItemID = I.StockItemID
0
RichardCorrieCommented:
in triggers u have 2 "virtual" tables Inserted and Deleted. in effect this indicates what is happening when a record is amended
when adding the inserted table has details of record inserted; Deleted is empty
when updating the inserted table has details of records updated; Deleted has equivalent records with values prior to update
when deleting, inserted table is empty, Deleted has records being deleted.

so to get the Delete trigger to work, change the reference to Inserted to Deleted.

SQL Books on Line (should have been installed) gives a v good summary of triggers etc.

/Richard
0
CABHughAuthor Commented:
Thanks for your help with this.

Sorry for being so dumb!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.