Stored procedure to generate auto incremented string primary key

Question

I just started with stored procedures and I want to create a string primary key with a constant part and an auto-incremental part. For example "REP101, REP102" REP103 ...".

Other methods that I found were to have two columns,an auto incremental int column and a primary key column with REP as a prefix. But I think it will be more efficient if I used stored procedures.

I am looking for a way of retrieving the last row in a table and returning its key using stored procedure so I can use it in the C# code. Any other way that can achieve the same result will be appreciated.

You have forgotten the question?

CharithJ
– CharithJ

2015-08-02 11:57:02 +00:00
Commented Aug 2, 2015 at 11:57 — CharithJ
– CharithJ, Commented Aug 2, 2015 at 11:57

Randy Minder · Accepted Answer · 2015-08-02 11:58:23Z

It seems to me that if every primary key value is going to have 'REP' as the prefix, then you really don't need the prefix at all, and you can simply use an Identity column as your primary key. Then you don't need a stored procedure at all, and this will be much more efficient.

gbjbaanb · Accepted Answer · 2015-08-02 12:00:09Z

A stored procedure would be more efficient. You'd use a Sequence to create the numeric part and then return it concatenated with the prefix.

roughly:

CREATE PROCEDURE GetNextKey @key VARCHAR(50) OUT AS BEGIN SET @key = 'REP' + SELECT NEXT VALUE FOR keys_seq; END;

However, why bother with the prefix in your primary key at all? Take a closer look at your design and htink it you really need to make the distinction. You can simply use a auto-increment ID as your primary key and add the prefix at point of display, if necessary.

Collectives™ on Stack Overflow

Stored procedure to generate auto incremented string primary key

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related