Microsoft SQL Server - default value provided by stored procedure

Question

Is it possible to have a non-null column where the value is generated at insert by calling a stored procedure the parameters of which are values passed to insert into the row?

For example, I have table User:

| username | name | surname | id |

Insert looks like this:

INSERT INTO USER (username, name, surname) 
VALUES ('myusername', 'myname', 'mysurname');

The id column is populated with an (integer) value retrieved by calling stored procedure mystoredproc with parameters myusername, myname, mysurname.

A further question is, would this stored procedure be called on each row, or can it be called in a grouped fashion. For example, I'd like my stored procedure to take the name and append a random integer to it so that that if I insert 100 users with the name 'David', they will get the same id and the stored procedure will be called only once. A bit of a bad example on the second point.

It would only be possible to insert one row at a time in this manner. — Tab Alleman, Jun 27 '18 at 13:42
How would you define column `id`? I can't remember how to define custom logic for a primary key which rather than auto-increments, calls a stored proc. What do I need to google for? Although, column `id` doesn't have to be primary key and in my extension, of course, can't be one. — s5s, Jun 27 '18 at 13:42
What do you mean "how would you define it"? You said it's an integer. What more definition does it need? — Tab Alleman, Jun 27 '18 at 13:45
If you're ready to google on... sounds like you want a User Defined Function :) — Mike M, Jun 27 '18 at 13:47
@TabAlleman How would you specify, in the table definition, that unless a value is provided at insert for `id`, it needs to call a stored procedure to generate one. — s5s, Jun 27 '18 at 13:47
yes define an IDENTITY on the `id` column and use an UDF to seed it: http://www.itprotoday.com/udfs-provide-new-identity — Cee McSharpface, Jun 27 '18 at 13:49
If you use a function instead of a stored procedure, you can call the function in a default constraint: https://dba.stackexchange.com/questions/163714/in-sql-server-how-do-you-create-a-default-constraint-for-a-column-using-the-res — Tab Alleman, Jun 27 '18 at 13:50
Also: https://stackoverflow.com/questions/30402167/use-a-udf-as-the-default-value-in-a-table-column-in-sql-server — Tab Alleman, Jun 27 '18 at 13:54
@TabAlleman thanks. I don't know why this post was voted down if I'm honest. — s5s, Jun 27 '18 at 14:01
This sounds like a really bad design to me. Are you trying to get a row number for each user? It sounds like you want to allow multiple rows for the same user. And when you generate a key like you are doing it is quite likely that you have created a race condition. Consider what happens when multiple people try to enter the same information. — Sean Lange, Jun 27 '18 at 14:31
You probably don't mean "random integer". You could insert a group of rows for "Marvin" and get, at random, "Marvin42". A subsequent insert of "Marvin" could randomly assign "Marvin42" or "Marvin54". Do you want to ensure that the new `id` does not already exist? — HABO, Jun 27 '18 at 19:03

Ronen Ariely · Accepted Answer · 2018-06-27T14:53:42.153

Good day,

Is it possible to have a non-null column where the value is generated at insert by calling a stored procedure

Option 1: please check if this work for you

Specify Default Value for the Column and use "NOT NULL"
create trigger on the table AFTER INSERT
Inside the trigger, you can use the virtual table "inserted" in order to get the inserted values.
Using these values (using the inserted table) you can update the column using the logic you need for all the rows at once

** there is no need to use external SP probably, but you can execute SP from trigger if needed

** All executed by a trigger is in the same transaction as the original query.

would this stored procedure be called on each row

NO! The trigger will be executed once for all rows you insert in the same statement. The inserted table includes all the rows which were inserted. In your update section (step 4) you can update all the rows which were inserted in once and no need to execute something for each row

** If you do use external SP which is executed from the trigger then you can pass it all the inserted table as one using Table-Valued Parameter

------------------- update ---------------

Here is a full example of using this logic:

drop table if exists T;
CREATE TABLE T (id int identity(2,2), c int NOT NULL default 1)
GO

CREATE TRIGGER tr ON T AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    UPDATE T SET T.c = T2.C + 1
    FROM inserted T2
    INNER JOIN T T1 ON T1.id = T2.id

END

INSERT T(c) values (1) -- I insert the value 1 but the trigger will change it to 1+1=2
select * from T
GO

-- test multiple rows:
INSERT T(c) values (10),(20),(30),(40)
select * from T
GO

I agree with other comments that this case sound like a bad design. This solution answer the question, but I highly recommend to think about re-design the system so you will not need to have this requirement/needs — Ronen Ariely, Jun 27 '18 at 14:51

score -1 · Answer 2 · answered Jun 27 '18 at 13:51

-1

DECLARE @rc INT = 0,
  @UserID INT = ABS(CHECKSUM(NEWID())) % 1000000 + 1;

WHILE @rc = 0
BEGIN
  IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE UserId= @UserId)
  BEGIN
    INSERT dbo.Users(UserId) WHERE Username = @UserName SELECT @UserId;
    SET @rc = 1; 
  END
  ELSE
  BEGIN
    SELECT @UserId = ABS(CHECKSUM(NEWID())) % 1000000 + 1,
      @rc = 0;
  END
END

answered Jun 27 '18 at 13:51

Murat Güzel

94
1
12

I don't know of such "Insert..... Where". Is this pseudocode? But more importantly, I'm not sure how this addresses the requirements... the OP wants to know how to add logic updating one column based on other columns in a single insert. – Mike M Jun 28 '18 at 20:42

Microsoft SQL Server - default value provided by stored procedure

2 Answers2