Unanswered: setting default value (concatenated string) of column using UDF
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
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()
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
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?
The reason why you're only getting "2" back is because you're defining your return value as "varchar". That is, varchar(1). Redefine it as something like varchar(30) and you'll get better output.
However, that still doesn't solve your real problem. If you run it now, you'll notice that it's not actually setting the value to the columns from the current row, but to the values of the last row. This is because the new row hasn't actually been inserted by the time the function is evaluated for the default, such that max(caseid) is actually referencing the last row you inserted.
I'd rather not set a default on this, and actually calculate the value when you do the insert from your stored procedure code. If that's not good enough for you, you can use an insert trigger instead. Something like:
create trigger TR_Update_SysInvNum on caseform
set SysInvNum = cast(caseyear as varchar(4))
+ cast(invNum as varchar(4))
+ cast(postId as varchar(1))
where caseid in (select caseid from inserted)
Not that I recommend you use a trigger, but it's the closest you'll get to a default value with your requirements.
I'll try our suggestion as per the return datatype definition (Varchar(30)). The database and table serve as a database for a .ADP Access application. So when a new form is opened the new sysinvnum needs to be pre-populated on the form. This is why I can't use a n after insert trigger, because the new sysinvnum won't be placed in the row until the row is commited of saved from the access end. However when you make it a default, it shows up immediately when a new form is created on the access end.
I switched the return value to Varchar(30) and I still get a "2"
Reffering to your comment about only updating the last row. That is actually what I want it to do, because when the user creates a new form in access, a new row (max caseid + 1) is created with a new "sysinvnum", so thats why I don't need to use a trigger to update the "sysinvnum" the sysinvnum never changes once set, becasue these fields are locked and read-only from the access end.
Notice that the first entry has a null sysinvnum, while the second entry has the sysinvnum that should've been in the first ("200500011" instead of "200500022"). That's what I meant. If that's what you want, then using the default will be fine. If it's not then you'll need to find something else that works for you instead.
I'd keep the data type as an int, but that may not be your best bet. The problem is that you have an arbitrary 4 digit length restriction, and there's no int type that's limited to that (tinyint goes to 255, smallint goes to 32767). Also, you need a zero-padded number, which as far as I know isn't available through any cast or convert primitive.
So, given that, what can you do? First off, because your int value can not be > 9999, you should add a CHECK constraint on your table enforcing this (you probably want to enforce that it isn't < 0 or < 1, too). Second, you need to write a function to turn an int into a zero-padded varchar(4). Something like this (minus error checking and initial sanity checks to make sure you're working with valid data, and without giving any guarantee as to performance):
create function ZeroPaddedInt (@int int)
declare @ret varchar(4)
set @ret = cast(@int as varchar(4))
while (len(@ret) < 4)
set @ret = '0' + @ret
Then you can use that function while building the SysInvNum string.
If you define the column as varchar, using + will implicitly convert to an int but you'll lose your leading zeros.