Setting Default Value (concatenated String) Of Column Using UDF

Hello,

I'm trying to set the default value of a column (SysInvNum) in a table (caseform) of mine by concatenating 3 other fields in the same table. These other fields are all Integer datatypes. they are "CaseYear" e.g. (2005), "InvNum" e.g. (0001) and "PostId" e.g. (5).

So basically the SysInvNum column for this row should read '200500015'

When I run a basic query using the CAST or CONVERT functions like this:

SELECT convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) from caseform

OR

SELECT cast(caseyear as varchar(4)) + cast(InvNum as varchar(4)) + cast(postid as varchar(1)) from caseform

I get the results I want. But since I want this value to be the default value of the column, I tried inserting this: convert (varchar,caseyear) + convert(varchar,InvNum) + convert(varchar,postid) into the default value parameter of the column in the caseform table. The result is a string that is the query itself.

I then tried creating a UDF called getsysinvnum() where I declare and set 2 variables whilst returning one of the variables as a varchar. An example of what it looks like is this:


CREATE FUNCTION GetSysInvNum()
RETURNS varchar
AS
BEGIN
DECLARE @maxcaseid Int
DECLARE @sysinvnum varchar

SELECT @maxcaseid = max (caseid) from caseform
SELECT @sysinvnum = cast(caseyear as varchar(4)) + cast(invnum as varchar(4)) + cast(postid as varchar(1)) from caseform where caseid = @maxcaseid
RETURN @sysinvnum
END


The result I get when I plug this into the default value of the column as : ([dbo].[getsysinvnum]()) is "2".

Yes it returns the number "2" could someone please tell me what I am doing wrong, or suggest a better way for me to do this?

Thanks in advance

'Wale