Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    9

    Unanswered: AutoGenerate No. in SQL Server 2000

    Hi
    I would like to create an Autogenerate function which has to do the following

    autogenerate field type is varchar(10). in that first 2 characters are purely character string. remaining will be numbers

    i'll pass the following parameters into the function
    1. tablename
    2. columnname
    3. 2 character string that has to build the first 2 characters
    eg: functionname(emp, empid, 'EM')

    Here the function has to execute and return the generated no.
    eg: EM1 - IF RECORDS NOT AVAIL IN THE TABLE
    EM5 - IF ALREADY RECORDS ARE AVAIL &THE MAX RECORD NO IS EM4.

    In this functioin i've to pass any tablename and corresponding field with the 2 character build string.... Already i tried. Few problems are there in passing the tablename as parameter...

    Anybody help me in that...

    Thanks in advance...

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    Hi,

    What were the problems you got?

    Madhivanan

  3. #3
    Join Date
    Jan 2005
    Posts
    9
    Hi,

    I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
    Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

    Function calling will be
    eg: functionname(tablename, columnname, 'PT')

    Give me the solution.

    Thx...

  4. #4
    Join Date
    Oct 2003
    Posts
    357
    Hi,

    Inside the function write something like this

    Declare @AutoGen varchar(10)

    if (select count(*) from @t) =0
    select @AutoGen = 'PT1'
    else
    select @AutoGen = 'PT'+convert(varchar(8),max(right(id,len(id)-2))+1) from tableName

    Madhivanan

  5. #5
    Join Date
    Jan 2005
    Posts
    9
    Hi,
    Actually i've got a problem to passing the Tablename as parameter.
    Its not working... U try to create a real function in SQL server 2000 with
    a table, test it, then... give me reply. then only u can get what'll be the problem...

    Tx in advance...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You will need to use dynamic SQL to do this by building your SQL Statement as a string and then EXECuting it.

    That said, do NOT do this. The way you are generating these codes goes against good principles of design, and my crystal ball tells me that this will be an endless source of trouble.

    I strongly urge you to reconsider your design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Posts
    9
    Hi...

    Anybody got the Autogenerate Nos. Function in SQL Server 2000.

    Already i mentioned the problems in the same thread....

    Help me

    Tx in Advance....

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Easy to do! Without your data I can't test this, but something as simple as:
    Code:
    CREATE PROCEDURE fubar
       @pcTable sysname
    ,  @pcColumn sysname
    ,  @pcPrefix CHAR(2)
    ,  @pcResult CHAR(10) OUTPUT
    AS
    
    if 'foo' = @pcTable AND 'bar' = @pcColumn
       SELECT @pcResult = @pcResult + Coalesce(Replace(' ', '0'
    ,     Str((SELECT Max(Convert(INT, SubString(foo.bar, 3, 8))
          FROM foo) + 1, 8)), '00000001')
    else if 'baz' = @pcTable and 'bat' = @pcColumn
       SELECT @pcResult = @pcResult + Coalesce(Replace(' ', '0'
    ,     Str((SELECT Max(Convert(INT, SubString(baz.bat, 3, 8))
          FROM baz) + 1, 8)), '00000001')
    else SELECT @pcResult = Repeat('?', 10)
    
    RETURN
    -PatP

  9. #9
    Join Date
    Jan 2005
    Posts
    9
    Sorry...

    In ur procedure why u checked the table name. I don't want to check the table name and column names. i'll just pass the table and column names with build string (2 character string). Thats all. The function has to return a no from the specific given table. (if rows are not avail) --> 'PT1'

    if rows avail--> 'PT5' (for example already the maximum value is PT4).

    It should be for all table... I've no. of tables. For each and every table i can not check it like ur procedure coding... got it...?

    Tx... Reconsider it and give the better solution...

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're trying to do something that SQL doesn't do well because it has other features that work SO much better. I'm sorry you don't like my solution, even though it does exactly what you asked for and relatively efficiently at that.

    Maybe someone else has a better answer. Personally, I'd just suggest that you simply use a better solution such as an IDENTITY column that would sidestep the need for this nonsense.

    -PatP

  11. #11
    Join Date
    Mar 2003
    Posts
    2

    Thumbs up Autogen Nos.

    select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from tableName



    Quote Originally Posted by pad
    Hi,

    I would like to create an Autogenerate Nos. (total length is varchar(10), in that first 2 letters purely characters, remainings are numerical value).
    Value has to start from 1. ie., if records not avail then...'PT1', if records avail then... 'PT4', 'PT5'... like that will be there... i'll pass the Tablename, Columnname and buildstring as parameter. The return value will be autogenerated no(varhchar(10)).

    Function calling will be
    eg: functionname(tablename, columnname, 'PT')

    Give me the solution.

    Thx...

  12. #12
    Join Date
    Jan 2005
    Posts
    9
    select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from @tableName

    here @tablename is the parameter. if i give like this its giving an error...
    please try to do the function, check it and give me the reply.

    Tx in advance...

  13. #13
    Join Date
    Jan 2005
    Posts
    9
    Hi...

    Anybody is having good solution for my question regarding this autogenerate nos. in SQL server 2000. Question is avail in this same thread...

    Good suggestions and Good & Efficient Solutions are welcome

    Tx in advance.

  14. #14
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    PatP gave you a good & efficient solution. If you don't understand, it is not due to not having the right solution.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

Posting Permissions

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