July 21, 2011 at 1:51 pm
Somewhere I've heard that SQL has aspects which are very much like a functional programming language. If so, I could really use that right now. I've got to remove some services which were entered into our database, and I already have a stored procedure which does exactly that, but one service at a time. What I would like to do is do a SELECT into a temporary table (or something like this), so that I can iterate over that collection, getting the ID I need from each record, and use that ID as the argument my SP expects to delete the service. The SP is used because it has to adjust the values in 3 different tables - just deleting the ID from the original table isn't enough. Naturally I want all of these deletes to occur within a transaction. And I want as many of these deletes to occur, so that it doesn't stop if it hits a snag somewhere in the middle of the collection.
So, does SQL work like a functional language? Can I write something, like a function, SP, or whatever, that I can pass in my deleting SP and the collection, and let it iterate over the collection applying the ID from each record to the passed in SP?
Kindest Regards, Rod Connect with me on LinkedIn.
July 21, 2011 at 2:09 pm
You can't pass a stored procedure to a function. You could pass the results of a stored procedure to a function if you wanted. Based on what you are describing I bet there i a set based solution to do what you are trying to do. If you can provide some ddl, sample data and detailed instructions about what you are trying to do there are lots of people here who will be willing to jump in and come with a solution. If you need some help or clarification about what and how to post this please read the link in my signature for question posting best practices.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 21, 2011 at 2:15 pm
How many records are we talking about here?
You certainly can do this row by row, however that method of processing is substantially slower than a set based solution. Also functions cannot make changes to any underlying objects, they are effectively read-only. They can create temp objects and manipulate data in them, BUT they cannot change a perisitent table or call a stored procedure. There are a lot of restrictions on functions.
However, a set based solution would be an all or nothing proposition, but a lot faster.
CEWII
July 21, 2011 at 2:36 pm
Elliott Whitlow (7/21/2011)
How many records are we talking about here?You certainly can do this row by row, however that method of processing is substantially slower than a set based solution. Also functions cannot make changes to any underlying objects, they are effectively read-only. They can create temp objects and manipulate data in them, BUT they cannot change a perisitent table or call a stored procedure. There are a lot of restrictions on functions.
However, a set based solution would be an all or nothing proposition, but a lot faster.
CEWII
59 records, right now. However, I suspect that users will enter more, even though they've been told not to. In fact, there were fewer records when I previously posted my question, so users are adding more, even though they've been told not to. So I've got to come up with a routine that can be applied for the next week or so.
Kindest Regards, Rod Connect with me on LinkedIn.
July 21, 2011 at 2:46 pm
Sean Lange (7/21/2011)
You can't pass a stored procedure to a function. You could pass the results of a stored procedure to a function if you wanted. Based on what you are describing I bet there i a set based solution to do what you are trying to do. If you can provide some ddl, sample data and detailed instructions about what you are trying to do there are lots of people here who will be willing to jump in and come with a solution. If you need some help or clarification about what and how to post this please read the link in my signature for question posting best practices.
Here's the DDL statement for the primary table that's going to drive this:
CREATE TABLE [VoucherServices](
[VoucherNumber] [int] NOT NULL,
[DateOfService] [datetime] NOT NULL,
[VoucherServiceCode] [smallint] NOT NULL,
[VoucherServiceUnits] [smallint] NOT NULL,
[ProviderNumber] [smallint] NULL,
[MoneyPool] [smallint] NULL,
[ServiceID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[InvoiceNumber] [int] NULL,
[AmountInvoiced] [money] NULL,
[GrossReceiptsTax] [smallmoney] NULL,
[DateInserted] [datetime] NULL,
CONSTRAINT [PK_VoucherServices_1__12] PRIMARY KEY CLUSTERED
(
[ServiceID] ASC
)
As I said, there's a couple other tables, which comprise the money pool, and the voucher. Basically, when the service gets deleted, then the money pool table needs to be adjusted by adding the money back to it, and the voucher needs to have the money added to it.
Here's a couple records for illustration:
VoucherNumberDateOfServiceVoucherServiceCodeVoucherServiceUnitsProviderNumberMoneyPoolServiceIDInvoiceNumberAmountInvoicedGrossReceiptsTaxDateInserted
707842011-07-01 13:00:00.00040441071850027NULL60.000.002011-07-05 13:09:53.317
678402011-07-05 12:30:00.00040421071850026NULL30.000.002011-07-05 13:07:15.563
Kindest Regards, Rod Connect with me on LinkedIn.
July 21, 2011 at 2:52 pm
Ahh so you need to modify a couple of other tables when a record deleted from the VoucherServices table? Probably the best solution for this is a delete trigger. Simple, set based and should be fast. If you can toss up the ddl for the other tables (or at least the column names [with datatypes]) that need to be adjusted I will be happy to put something together. Sounds like we add this AmountInvoiced to the MoneyPool and added to the voucher?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 21, 2011 at 3:01 pm
Correct, the AmountInvoiced and GrossReceiptsTax, summed together gets added to the voucher and moneypools table. Here are the other 2 tables. Note: I've only included relevant columns:
CREATE TABLE [dbo].[Vouchers](
[VoucherNumber] [int] NOT NULL,
[ClientNumber] [int] NOT NULL,
[MoneyPool] [smallint] NOT NULL,
[VoucherCap] [money] NOT NULL,
[AmountExpended] [money] NOT NULL,
CONSTRAINT [PK_Voucher99] PRIMARY KEY CLUSTERED
(
[VoucherNumber] ASC,
[ClientNumber] ASC
)
CREATE TABLE [dbo].[MoneyPools](
[FiscalYear] [smallint] NOT NULL,
[MoneyPool] [smallint] NOT NULL,
[RemainingValue] [money] NOT NULL,
CONSTRAINT [PK_MoneyPools_3__17] PRIMARY KEY CLUSTERED
(
[FiscalYear] ASC,
[MoneyPool] ASC
)
Kindest Regards, Rod Connect with me on LinkedIn.
July 21, 2011 at 3:03 pm
Alright unless somebody else jumps in I will take a look in the morning. Gotta get the kids to their swimming lesson. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 21, 2011 at 3:11 pm
Rod at work (7/21/2011)
Correct, the AmountInvoiced and GrossReceiptsTax, summed together gets added to the voucher and moneypools table. Here are the other 2 tables. Note: I've only included relevant columns:
CREATE TABLE [dbo].[Vouchers]([VoucherNumber] [int] NOT NULL,
[ClientNumber] [int] NOT NULL,
[MoneyPool] [smallint] NOT NULL,
[VoucherCap] [money] NOT NULL,
[AmountExpended] [money] NOT NULL,
CONSTRAINT [PK_Voucher99] PRIMARY KEY CLUSTERED
(
[VoucherNumber] ASC,
[ClientNumber] ASC
)
CREATE TABLE [dbo].[MoneyPools](
[FiscalYear] [smallint] NOT NULL,
[MoneyPool] [smallint] NOT NULL,
[RemainingValue] [money] NOT NULL,
CONSTRAINT [PK_MoneyPools_3__17] PRIMARY KEY CLUSTERED
(
[FiscalYear] ASC,
[MoneyPool] ASC
)
How are you going to enforce a FK Constraint On money pool? Or did you just exclude this column?
You only have one of the two columns in the parent table.
Is the AmountExpended column a running total not to exceed the VoucherCap?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 22, 2011 at 8:12 am
Any chance you can create some sample data (preferably insert statements)? That will let me get to work quickly on the problem at hand instead of spending my time getting this all setup.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2011 at 2:08 pm
Sorry, Sean, I'm getting hammered today. OK, here's a couple quick inserts to get the Vouchers and MoneyPools tables populated.
insert into Vouchers
values (10000, 149566, 1, 4000.00, 150.00)
insert into Vouchers
values (10001, 100456, 1, 4000.00, 75.00)
insert into MoneyPools
values (2012, 1, 500000.00)
Kindest Regards, Rod Connect with me on LinkedIn.
July 22, 2011 at 2:20 pm
It is good that you only have 59 records in your table.
I suggest that you try and get the design correct before you load a lot of data.
One reason is that if you create a good design it minimizes the amount of code that you have to write once the application is deployed to production.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 22, 2011 at 3:19 pm
I have to agree with WelshCorgi that your table structures are less than optimal, of course there are some other pieces of this that you mentioned are not here. Given the structure that you have gave as as your ddl i created a delete trigger that I think does what you need it to do.
/*
truncate table VoucherServices
truncate table Vouchers
truncate table MoneyPools
insert VoucherServices
select 10000, '2011-07-18 09:32:00.000', 500, 2, 117, 1, 889394, 60.00, 0.00, '2011-07-19 11:09:31.567'
insert into Vouchers
values (10000, 149566, 1, 4000.00, 150.00)
insert into Vouchers
values (10001, 100456, 1, 4000.00, 75.00)
insert into MoneyPools
values (2012, 1, 500000.00)
*/
/*
select * from VoucherServices
select * from Vouchers
select * from MoneyPools
*/
go
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[VoucherServices_Delete]'))
DROP TRIGGER [dbo].[VoucherServices_Delete]
go
create trigger VoucherServices_Delete on VoucherServices for Delete as
begin
--subtract the AmountExpended by the sum of AmountInvoiced and GrossReceiptsTax
update Vouchers set AmountExpended = AmountExpended - (select SUM(ISNULL(AmountInvoiced, 0.0) + ISNULL(GrossReceiptsTax, 0.0)) from deleted d2 where d2.ServiceID = d.ServiceID)
from deleted d
join Vouchers v on v.VoucherNumber = d.VoucherNumber
--delete Vouchers records that now have expended = 0
delete Vouchers
from deleted d
join Vouchers v on v.VoucherNumber = d.VoucherNumber
where AmountExpended <= 0
--add the money back to the money pool
update MoneyPools set RemainingValue = RemainingValue + (select SUM(ISNULL(AmountInvoiced, 0.0) + ISNULL(GrossReceiptsTax, 0.0)) from deleted d2 where d2.ServiceID = d.ServiceID)
from deleted d
join MoneyPools m on m.MoneyPool = d.MoneyPool
end
go
--delete VoucherServices where ServiceID = 1
select * from VoucherServices
select * from Vouchers
select * from MoneyPools
There is a lot of testing/debugging code throughout. Make sure you understand what is going on with before you put it on a live system. Remember that you are the one who will be supporting it at 3am if something goes wrong. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2011 at 3:28 pm
Sean,
I agree with your all of your post but that DB design is not mine. 😉
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply