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...Hi,

What were the problems you got?

Madhivanan|||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...|||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|||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...|||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.|||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...|||Easy to do! Without your data I can't test this, but something as simple as: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|||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...|||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|||select 'PT'+convert(varchar(8),max(isnull(right(id,len(id )-2),0))+1) from tableName

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...|||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...|||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.|||PatP gave you a good & efficient solution. If you don't understand, it is not due to not having the right solution.