0

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.

Improve this question
12
  • It would only be possible to insert one row at a time in this manner. Commented Jun 27, 2018 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. Commented Jun 27, 2018 at 13:42
  • What do you mean "how would you define it"? You said it's an integer. What more definition does it need? Commented Jun 27, 2018 at 13:45
  • 1
    If you use a function instead of a stored procedure, you can call the function in a default constraint: dba.stackexchange.com/questions/163714/… Commented Jun 27, 2018 at 13:50
  • 1
    Also: stackoverflow.com/questions/30402167/… Commented Jun 27, 2018 at 13:54

2 Answers 2

Reset to default
2

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

  1. Specify Default Value for the Column and use "NOT NULL"
  2. create trigger on the table AFTER INSERT
  3. Inside the trigger, you can use the virtual table "inserted" in order to get the inserted values.
  4. 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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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
-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
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.