October 5, 2010 at 7:33 pm
Firstly, please don’t shoot the messenger here, I have inherited the database structure I am about to describe and know full well its BAD, but it is what I have inherited and I am limited by 3rd party forces so I can’t change it.
I have a “Products” table. The “Products” table has approx 20 “Part No xx” and associated “Part Price xx” fields defined (yes I know, bad design). I also have a master “Parts” table which contains all part no’s and their prices.
I have a requirement to update all “Part Price xx” fields in the “Products” table on a regular basis.
I know I can achieve what I need using cursors, but would prefer to avoid using them due to the performance hit. So I have written a simple function which returns the price of the part no supplied. I have also written a stored procedure with an Update statement where the value of every “Part Price xx” field is supplied by the value returned from the function.
Pretty basic, however, I do need to track errors which is where it becomes difficult.
If the function returns a zero value due to the Part No supplied not being available in the master parts file, I need to record this somewhere so it can be investigated. My idea was to create a Temporary table in stored procedure prior to executing the Update statement which calls the function. When the function can't find the supplied Part No, it would Insert a record in the Temp table. Once the Update statement in the calling stored procedure finished, I would simply export the temp table using BCP.
And therein lye’s my problem. A function cannot run any statement that might alter a database table (e.g. Insert, Update, Delete or even Execute and sp_executesql) as it produces an error similar to this;
Cannot access temporary tables from within a function.
Or
Only functions and extended stored procedures can be executed from within a function.
Can anyone suggest how I might be able to record any part no’s that is not found by the function?
Below is a sample of the SP & Function.
The only thing I could think of is if there something clever we can do with the “SET” statement in the Update statement so that it Insert a record in the Temp table if the function return a zero value otherwise set the value of the field to the value returned by the function? But I don’t profess to being an SQL expert, therefore my post.
CREATE PROCEDURE [dbo].[usp_Update_Part_Prices]
AS
BEGIN
SET NOCOUNT ON;
-- Create the temp table to store any missing part no’s.
Create Table #UpdatePartsErrors ([Product Id] int, [Part No] nvarchar(20))
Update [Products]
Set[PartPrice1] = dbo.fn_Part_Price([Product ID], [Part1 Part No],
Set[PartPrice2] = dbo.fn_Part_Price([Product ID], [Part2 Part No],
Set[PartPrice3] = dbo.fn_Part_Price([Product ID], [Part3 Part No],
Set[PartPrice4] = dbo.fn_Part_Price([Product ID], [Part4 Part No],
Set[PartPrice5] = dbo.fn_Part_Price([Product ID], [Part5 Part No],
Set[PartPrice6] = dbo.fn_Part_Price([Product ID], [Part6 Part No],
Set[PartPrice7] = dbo.fn_Part_Price([Product ID], [Part7 Part No],
Set[PartPrice8] = dbo.fn_Part_Price([Product ID], [Part8 Part No]
-- Export the temp table using BCP.
END
CREATE FUNCTION [dbo].[fn_Part_Price]
(
@argProductId as integer,
@argPartNo as nvarchar(20),
)
RETURNS smallmoney
AS
BEGIN
Declare @fnPartPrice smallmoney
Set @fnPartPrice = 0
Set @fnPartPrice = (Select [Unit Price] From [Parts]
Where [Part No] = @argPartNo)
Set @fnPartPrice = IsNull(@fnPartPrice, 0)
If @fnPartPrice = 0
Begin
Set@Sqlcmd = 'Insert Into #UpdatePartsErrors Values(' +
Char(39) + Cast(@argProductId as nvarchar) + Char(39) + ', ' +
Char(39) + @argPartNo + Char(39) + ')'
--Exec (@Sqlcmd)
Exec sp_executesql @Sqlcmd
End
-- Return the result of the function
ExitFunction:
Return (@fnPartPrice)
END
Many thanks for your suggestions,
Doug
October 6, 2010 at 12:22 am
I managed to find a solution.
I'm using xp_cmdshell to execute an Echo statement to create and subsequently append to the error file. I doubt this is ideal and I dont like the fact I have use an old DOS command to achieve this, but in actual fact it works really well and the performance is actually quite good. The function now looks like this;
CREATE FUNCTION [dbo].[fn_Part_Price]
(
@argProductId as integer,
@argPartNo as nvarchar(20),
)
RETURNS smallmoney
AS
BEGIN
Declare @fnPartPrice smallmoney
Declare @ErrorMessage nvarchar(255)
Set @fnPartPrice = 0
Set @fnPartPrice = (Select [Unit Price] From [Parts]
Where [Part No] = @argPartNo)
Set @fnPartPrice = IsNull(@fnPartPrice, 0)
If @fnPartPrice = 0
Begin
Set @ErrorMessage = 'Part not found in Parts table.'
-- Define the "Echo" command to append to the Error File which is created in the calling SP
Set @echoStr = 'Echo ' +
@argProductId + Char(9) +
@argPartNo + Char(9) +
@ErrorMessage +
' >> “c:\PartPriceErrs.txt"'
Exec xp_cmdshell @echoStr
End
-- Return the result of the function
ExitFunction:
Return (@fnPartPrice)
END
This code appends a tab delimited string to the text file so the file can be easily opened in Excel.
I also added a corresponding xp_cmdshell command in the the calling SP prior to executing the "Update" statement which creates the file and the header row. The syntax in the SP is as follows; Note the single ">" which creates the file, a double ">>" as seen in the function appends to the file.
Declare @echoStrvarchar(4000)
Set @echoStr = 'Echo Product Id' + Char(9) + 'Part No' + Char(9) + 'Error Message' + ' > ' “c:\PartPriceErrs.txt"'
Exec xp_cmdshell @echoStr
Cheers
Doug
October 9, 2010 at 12:11 pm
I think a better solution would be to create a temp table to hold the contents of the update statement and then use the OUTPUT clause in your update statement. Then go through the temp table looking for zero prices. Something like:
CREATE TABLE #T
( PKValue INT
, Price MONEY
)
UPDATE XYZ
SET XYZ.Price = <Funtion Value>
OUTPUT inserted.PKValue, inserted.Price INTO #T
Now go through the #T table and look for zero prices.
Todd Fifield
October 10, 2010 at 4:08 pm
Thanks Todd
Sounds like a neat solution. I'll certainly look into.
Cheers
Doug
October 10, 2010 at 5:43 pm
Gosh... the function doesn't even use the ProductID for anything except an error message. Are you sure that's right?
I'd also be tempted to make one pass per column instead of using a function because then I could do the correct logging, etc, and maybe do it all a little quicker using some "lasagne code" (layered code).
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2010 at 6:38 pm
Agreed -
The code looks a bit longer, but it would probably work a LOT faster to skip the function entirely and build this out as a direct SQL statement. You've already into the design choice of making functions "touchless". Unfortunately the also have a nasty habit of slowing down code a LOT if used in the way you're investigating. You end up getting much faster response when you remove the function.
I can't help but notice you're also updating the table even IF you pull up a 0 value. Running single column updates would actually make it much easier to not put in invalid values.
This tends to look like a hammer, but it tends to get you through the stuff awfully fast, simply by using 20 states akin to what's below.
update pr
set [PartPrice1] = [Unit Price]
from PRODUCTS pr
inner join parts on pr.[Part1 Part No]=[Part No]
where [Unit Price] >0
hunt for either nulls or prices that are still 0 would be rather easy this way (again, depending on what you are getting).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2010 at 7:06 pm
You seem too have two requirements:
1) Update all “Part Price xx” fields in the “Products” table.
2) Report all “Part No xx” that do not exist in the master parts file
Suggest that this should be two different SQL Statements.
For Update all “Part Price xx” fields in the “Products” table, see the below SQL which:
Joins Product_XX to the Part table
Then pivots the result
Then updates the Product_XX
The SQL has not been thoughly tested , so take some care.
UpdateP_XX
setCost_01 = COALESCE(NewPrices.UnitPrice_01,Cost_01)
,Cost_02 = COALESCE(NewPrices.UnitPrice_02,Cost_02)
,Cost_03 = COALESCE(NewPrices.UnitPrice_03,Cost_03)
,Cost_04 = COALESCE(NewPrices.UnitPrice_04,Cost_04)
fromProduct_XX as P_XX
join
(select Product_XX.ProductID
,MAX( CASE when [Parts].[Part No] = Part_01 THEN [Parts].[Unit Price] ELSE NULL END ) as UnitPrice_01
,MAX( CASE when [Parts].[Part No] = Part_02 THEN [Parts].[Unit Price] ELSE NULL END ) as UnitPrice_02
,MAX( CASE when [Parts].[Part No] = Part_03 THEN [Parts].[Unit Price] ELSE NULL END ) as UnitPrice_03
,MAX( CASE when [Parts].[Part No] = Part_04 THEN [Parts].[Unit Price] ELSE NULL END ) as UnitPrice_04
FROMProduct_XX
join[Parts]
on[Parts].[Part No] in (Part_01,Part_02,Part_03,Part_04)
group by Product_XX.ProductID
) as NewPrices
onP_XX.ProductID = NewPrices.ProductID
SQL = Scarcely Qualifies as a Language
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply