Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: 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

  2. #2
    Join Date
    Oct 2005
    Posts
    5
    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:

    Code:
    create trigger TR_Update_SysInvNum on caseform
    after insert
    as
    begin
        update 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)
    end
    Not that I recommend you use a trigger, but it's the closest you'll get to a default value with your requirements.

  3. #3
    Join Date
    Mar 2002
    Posts
    162
    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.
    Last edited by Wale; 10-24-05 at 01:28.

  4. #4
    Join Date
    Mar 2002
    Posts
    162
    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.

    Any other suggestions?

  5. #5
    Join Date
    Mar 2002
    Posts
    162
    oops, sorry, I've fixed it. I forgot to re-define the declared variable @sysinvnum as varchar(10) also. Thanks for your help.

  6. #6
    Join Date
    Oct 2005
    Posts
    5
    You're defining the varchar return value in two places -- as part of the signature of the function, and as the local variable within the function. Thus, you need to fix it in two places.

    As for creating SysInvNum with the previous values, I just want to make sure you're understanding the problem. Starting from an empty table, insert two rows:

    Code:
    insert into caseform (caseyear, invnum, postid)
    values (2005, '0001', 1)
    insert into caseform (caseyear, invnum, postid)
    values (2005, '0002', 2)
    Now look at the output:

    Code:
    caseid    caseyear   invnum postid   sysinvnum
    --------------------------------------------
    1           2005        0001   1           NULL
    2           2005        0002   2           200500011
    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.

  7. #7
    Join Date
    Mar 2002
    Posts
    162
    hmm, I hear ya, I have an idea, let me try it out first, i'll get back to you with the results

  8. #8
    Join Date
    Mar 2002
    Posts
    162
    since the other 3 columns (used in the concatenation) are also determined by UDF's I decided to use them in the sql, so it basically went like this:

    SELECT @sysinvnum = convert(varchar(4),getcaseyear(@date()) + convert(varchar(4),getInvnum(@date()) + convert(varchar(1),getpostId())

    and then I returned the result into a Varchar(10) datatype. And it works fine. Thanks!!

    if any situation should arise concerning the first row, then I'll just seed it with dummy data, that no one can see and move on from there. Thanks for mentioning.
    Last edited by Wale; 10-24-05 at 02:28.

  9. #9
    Join Date
    Mar 2002
    Posts
    162
    one more thing. my current values for Invnum starts at 1000, hence 1001, 1002 , 1003 etc, but the requirement states that they would prefer a 0001, 0002, 0003 etc..............

    do you know what datatype would be suitable for such data, that can also be mathematically incremented?

  10. #10
    Join Date
    Oct 2005
    Posts
    5
    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):

    Code:
    create function ZeroPaddedInt (@int int)
    returns varchar(4)
    as
    begin
        declare @ret varchar(4)
        set @ret = cast(@int as varchar(4))
    
        while (len(@ret) < 4)
        begin
            set @ret = '0' + @ret
        end
    
        return @ret
    end
    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.

    Code:
    select '0001' + 1
    select dbo.ZeroPaddedInt('0001' + 1)     
                
    ----------- 
    2
    
    (1 row(s) affected)
    
         
    ---- 
    0002
    
    (1 row(s) affected)

  11. #11
    Join Date
    Mar 2002
    Posts
    162
    Worked perfectly. Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •