Get SQL Computed Column Inserted Value

Question

My Table Structure as follow,

CREATE TABLE tbl_Info
(
    [SSEID]         BIGINT              NOT NULL    IDENTITY(1,1),
    [ShortenKey]    AS ConvertToBase([SSEID]),
    [Title]         VARCHAR(500)        NULL,       
)

ConvertToBase Function as Follow,

CREATE FUNCTION ConvertToBase(@Number BIGINT)
RETURNS VARCHAR(15)
AS 
BEGIN
      // implementation
END

I need to get the generated [ShortenKey] value after INSERT query in sp. how to do this ?

score 3 · Accepted Answer · answered Jun 15 '11 at 08:21

3

Use the OUTPUT clause?

INSERT tbl_Info (Title)
OUTPUT INSERTED.ShortenKey
VALUES ('new title')

Note: may not work with computed columns says MSDN, if I read it correctly.

answered Jun 15 '11 at 08:21

gbn

422,506
82
585
676

Thanks for the response, but this error..."Column 'inserted.ShortenKey' cannot be referenced in the OUTPUT clause because the column definition contains a subquery or references a function that performs user or system data access. A function is assumed by default to perform data access if it is not schemabound. Consider removing the subquery or function from the column definition or removing the column from the OUTPUT clause. " – Sency Jun 15 '11 at 08:23
2

Add WITH SCHEMABINDING to the fucntion – gbn Jun 15 '11 at 08:35

score 1 · Answer 2 · answered Jun 15 '11 at 08:39

1

Use SCOPE_IDENTITY to get the new identity value. Then query the newly inserted row.

SELECT ShortenKey
FROM dbo.tbl_Info
WHERE SSEID = SCOPE_IDENTITY()

answered Jun 15 '11 at 08:39

Anthony Faull

17,549
5
55
73

@gbn My code sample returns ShortenKey. Am I missing something? – Anthony Faull Jun 15 '11 at 13:27

score 1 · Answer 3 · edited Mar 05 '12 at 12:39

1

SELECT ShortenKey
FROM dbo.tbl_Info
WHERE SSEID = SCOPE_IDENTITY()

edited Mar 05 '12 at 12:39

DaveShaw

52,123
16
112
141

answered Mar 05 '12 at 10:49

sarvan

91
6

Get SQL Computed Column Inserted Value

3 Answers3

Linked

Related