Inline SQL function which sometimes must update database

Question

Is it possible to create a function/procedure, which could be used in a SQL statement like this:

INSERT INTO Journal(ProductID,Quantity) VALUES(LookupOrCreateProduct('12345678'),5)

LookupOrCreateProduct should look up a product table by string (barcode) and:
* If barcode is found - return Product ID
* If barcode is not found - create a new record in Products table with new barcode and return its ID

I explored SQL Server functions, but they do not allow INSERTs or any other database modification inside function body. Stored procedures can return values, but they can only be of int type. My ID column is bigint. Another option is to use output parameter but then it is not clear to me, how can I inline it in SQL statement. Thank you.

Set output parameter of Bigint datatype and return it from stored procedure and save it into variable and then use it for your insert query... — Krishnraj Rana, Sep 03 '14 at 15:49
You can't do that with a function. [And you should use an OUTPUT parameter, not a RETURN value](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-using-select-or-return-instead-of-output.aspx). RETURN values are for error / status codes, not for data; one of the primary reasons is exactly what you've discovered: they're only useful for ints. — Aaron Bertrand, Sep 03 '14 at 16:25

M.Ali · Accepted Answer · 2014-09-03T16:49:49.690

2

CREATE PROCEDURE LookupOrCreateProduct 
   @BarCode    VARCHAR(100),
   @ProductID  BIGINT OUTPUT
AS
BEGIN
   SET NOCOUNT ON;


       SELECT TOP 1 @ProductID = ProductID
       FROM dbo.Products 
       WHERE BarCode = @BarCode

   IF(@ProductID IS NULL)
    BEGIN
      INSERT INTO dbo.Products(Barcode)
      VALUES (@BarCode)

      SET @ProductID = SCOPE_IDENTITY();
    END   

END

edited Sep 03 '14 at 16:49

answered Sep 03 '14 at 15:55

M.Ali

67,945
13
101
127

Why the `IF EXISTS` check? Even if BarCode is indexed, this is a wasteful extra check. `SELECT @ProductID =` ... `IF @ProductID IS NULL` then insert. – Aaron Bertrand Sep 03 '14 at 16:27
@AaronBertrand Certainly it makes more sense this way, thank you for the pointer :) – M.Ali Sep 03 '14 at 16:50

score 1 · Answer 2 · answered Sep 03 '14 at 15:49

I think the best you can do is an output parameter in a stored procedure:

declare @product_id int;

begin transaction;

exec dbo.LookupOrCreateProduct '12345678', @product_id out;

insert into journal (productId, quantity) values (@product_id, 5);

commit transaction;

Inline SQL function which sometimes must update database

2 Answers2