User Defined Functions as Default Value for column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

User Defined Functions as Default Value for column

Author

Topic

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-21 : 06:30:11

I am working in sql server 2008 R2.
I have a scalar valued function which returns a document no.
Return value datatype is varchar.
I want to set that function as a default value or the value for one of the column of my table.

Can we assign function as a default value?
If yes how to do?

Can anybody help me out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 06:42:24

you can do it. it will be like
CREATE TABLE tablename
(
...other columns,
VarcharColumn AS dbo.ScalarUDF(parameters...)
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-21 : 08:56:34

Thanks Vishakh,

Creating a table it is working fine.

But after inserting values, function returned value is not storing
in the table.

And then i am not able to even select the rows.
It is showing Error.

like my table is

CREATE table ABC
{ DocId int,
DocNo As dbo.GetNewDocNo(),
other columns
}

and my function is

ALTER FUNCTION [dbo].[GetNEWDocNo]
(
-- Add the parameters for the function here

)
RETURNS varchar(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @docno varchar(50),@rowcnt int;

-- Add the T-SQL statements to compute the return value here
SELECT @rowcnt=COUNT(*) FROM DocMaster; -- For First Entry in table
IF @rowcnt=0
BEGIN
SET @docno=1
END
IF @rowcnt>0
BEGIN
SELECT @docno=MAX(DocNo) FROM DocMaster -- For all next entries in the table
SET @docno=@docno+1
END
-- Return the result of the function
RETURN @docno
END

and the error i am getting is
**** Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). ****

Function returns varchar because further I want to add some Prefix to the DocNo.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-21 : 10:05:34

You should be using an identity column for this.

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-21 : 10:13:25

Russell

iam using identity for DocId not for DocNo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 10:20:27

were you using a normal insert or trying to do something recursively?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-21 : 13:24:30

IF @rowcnt=0
BEGIN
SET @docno=1
END
IF @rowcnt>0
BEGIN
SELECT @docno=MAX(DocNo) FROM DocMaster -- For all next entries in the table
SET @docno=@docno+1

That code does the same thing that an identity column does. Would be better to store the next available docno in a table -- either with an identity column, or (within an explicit transaction), increment and return the value.

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-22 : 00:40:23

Vishakh,

I am using the normal insert only.
it is inserting the row for first time.
for first condition
@rowcnt=0;
but afterwords I could not select the rows from table nor i could add more rows to the table. For these operations it is giving the error which i mentioned in previous post.

I can not set it as identity because next step is to put prefix for DocNo. that is DocNo will be like "Prefix00001".
Then it is not an int, we set identity for number column only.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 00:52:29

just for setting prefix you dont need to do this. as Russell suggested you can make an identity column say ID then create a computed column based on that like


CREATE TABLE tablename
(...,
ID int IDENTITY(1,1),
DocNo AS 'Prefix' + RIGHT('00000' + CAST(ID AS varchar(10)),5)
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-22 : 01:42:14

Thanks aaaaaaaaaaa lotttttttttttt
Vishakh and Russell.
It is working fine and smoothly.
Thanks again.

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-22 : 07:56:52

Vishakh

I have gone through your post on composable DML
and I tried to do it but it is not working....

here is what I did

INSERT INTO Child

(Parent_ID,Value2,Value3)

SELECT

FROM ID,

'ABC',

'PQR'

(

INSERT INTO Parent (Value1)

OUTPUT INSERTED.ID

VALUES('LMN')

)t

Errors
are

Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.

How to correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-22 : 13:21:20

are you on sql 2008 or above. Composable DML works only on SQL 2008 and above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

manisha.vk
Starting Member

17 Posts

Posted - 2011-09-24 : 00:30:46

Ya I am on sql server 2008 R2.

It is working when I changed code as

INSERT INTO Child(Parent_ID, Value2, Value3)
SELECT Parent_ID, Value2, Value3
FROM (
INSERT INTO Parent (Value1)
OUTPUT inserted.ID,'Second','Third'
values ('First')
) AS T(Parent_ID,Value2,Value3)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-24 : 03:28:24

quote:
Originally posted by manisha.vk

Ya I am on sql server 2008 R2.

It is working when I changed code as

INSERT INTO Child(Parent_ID, Value2, Value3)
SELECT Parent_ID, Value2, Value3
FROM (
INSERT INTO Parent (Value1)
OUTPUT inserted.ID,'Second','Third'
values ('First')
) AS T(Parent_ID,Value2,Value3)

oh you had some constant values. Thats why it didnt work as it need an alias

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources