SQL Server Performance Forum – Threads Archive

Functions

Is it possible to create a scalar function to use as a default constraint as follows? The function cannot be created as the "delete" statement is not allowed in a scalar function. Is there a way around this? i.e. Is it possible to fool SQL into thinking a stored procedure is an extended stored procedure and have the "delete" statement in this? More than one row maybe inserted into Product table at a time, i.e. a trigger would be slow and require a cursor. Any help greatly appreciated. i.e.
CREATE TABLE Product
(
….
ProductCode INT DEFAULT (fnGetNextAvailableProductCode()) NOT NULL
)
GO
ALTER TABLE Product
ADD CONSTRAINT [IX_ProductCode] UNIQUE NONCLUSTERED
(
[ProductCode]
)
GO
CREATE FUNCTION GetNextAvailableProductCode ()
RETURNS INT
AS
BEGIN
DECLARE @ProductCode INT SELECT @ProductCode = Min(ProductCode)
FROM zs_AvailableProductCodes
— Now remove used item from list of Available Product Codes —
DELETE FROM
zs_AvailableProductCodes
WHERE ProductCode = @ProductCode RETURN(@ProductCode)
END
GO

---

Have you tried calling an sproc to do the delete from the function?
Or since you are returning the @productcode from the function, do the delete in the calling calling process.

---

As I was unable to fin a solution using a function for the task I wished to carry out, I resorted to using an after trigger that loops through a cursor. If anyone can think of a better approach, please let me know. Regards,
Rik

---

]]>