Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Calling user defined functions with Create Table

Tags:

sql

sql-server

I have the following function

CREATE FUNCTION GetIdentity() RETURNS INT AS
BEGIN
  RETURN (IDENT_CURRENT('tblTempPo'))
END
GO

I need to call it with create table

create table tblTempPo
(
ID int null,
 BrickVolume AS
              (
              GetIdentity() 
              )
)

I'm getting the error

'GetIdentity' is not a recognized built-in function name.

How can I solve this?

like image 613
chamara Avatar asked Nov 04 '22 07:11

chamara


1 Answers

You need to add dbo (or whatever the schema name is) to properly call the function:

create table tblTempPo
(
    ID int null,
    BrickVolume AS(dbo.GetIdentity())
)

Although, for your example to work, you'd want to do something like this:

CREATE TABLE tblTempPo
(
    ID INT IDENTITY(1,1) NOT NULL,
    AnotherField VARCHAR(10),
    BrickVolume AS (dbo.GetIdentity())
)
GO

INSERT INTO tblTempPo VALUES('a')
INSERT INTO tblTempPo VALUES('b')
INSERT INTO tblTempPo VALUES('c')
SELECT * FROM tblTempPo

The SELECT statement will yield the results:

ID     AnotherField     BrickVolume
-----------------------------------
 1          a               3
 2          b               3
 3          c               3
like image 111
rsbarro Avatar answered Nov 07 '22 21:11

rsbarro