SQL Server Performance Forum – Threads Archive
FunctionsIs 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
ALTER TABLE Product
ADD CONSTRAINT [IX_ProductCode] UNIQUE NONCLUSTERED
CREATE FUNCTION GetNextAvailableProductCode ()
DECLARE @ProductCode INT SELECT @ProductCode = Min(ProductCode)
— Now remove used item from list of Available Product Codes —
WHERE ProductCode = @ProductCode RETURN(@ProductCode)
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,