To get a sequential varchar column in database

Posted by Nkkppp under Sql Server on 2/17/2013 | Points: 10 | Views : 984 | Status : [Member] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

Below is my requirement.

I have an Identity column in a table called QuoteID and another column QuoteNo.So depending on the QuoteID the QuoteNo should also be displayed in the front end. For eg : i need something like if QuoteID=1 QuoteNo= QNO20000101 ; QuoteID=2 QuoteNo=QNO20000102.

I have done achived this in the front end like getting the max(QuoteID) from database and converting it to int and adding it to 20000100.So that the value keeps on increasing.

I would like to know if two users are saving the data into database.Will there be any problem like same QuoteNo for QuoteID 1 and QuoteID 2.Becuase QuoteID is an identity column so only the textbox text i.e. QuoteNo will be saved in a new row.

Please suggest a better way to achieve the same if my approach is not that good.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Peermohamedmydeen on: 2/18/2013 [Member] Bronze | Points: 25

0	Hi, Lets split the functionality into two different proc (lets assume you have 2 procs for this purpose). When you say 'New Quotation' from the front end, the first proc would create an empty row in the table with a QuoteID and insert the temporary column values for example the concatinated QuoteNo and return the QuoteNo to the UI. Now while saving the quote, the another proc would update the same quoteID we created before. If you want to achieve the functionality with a single proc, use a proxy proc which will have calls to 2 procs with a transaction mechanism to lock the identity. Regards, PMM Nkkppp, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

Lets split the functionality into two different proc (lets assume you have 2 procs for this purpose). When you say 'New Quotation' from the front end, the first proc would create an empty row in the table with a QuoteID and insert the temporary column values for example the concatinated QuoteNo and return the QuoteNo to the UI.

Now while saving the quote, the another proc would update the same quoteID we created before.

If you want to achieve the functionality with a single proc, use a proxy proc which will have calls to 2 procs with a transaction mechanism to lock the identity.

Regards,
PMM

Nkkppp, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts