Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm not suggesting over what you have currently but, i'd do this way. This is the way I've implemented in my application. Which im gonna give you. Hope you Like this. This is fully Dynamic and Works for all the Transaction you could have.</p> <p>I've a table Which hold the Document Number as :</p> <pre><code>CREATE TABLE INV_DOC_FORMAT( DOC_CODE VARCHAR(10), DOC_NAME VARCHAR(100), PREFIX VARCHAR(10), SUFFIX VARCHAR(10), [LENGTH] INT, [CURRENT] INT ) </code></pre> <p>Which would hold the Data Like :</p> <pre><code>INSERT INTO INV_DOC_FORMAT(DOC_CODE,DOC_NAME,PREFIX,SUFFIX,[LENGTH],[CURRENT]) VALUES('01','INV_UNIT','U','',5,0) INSERT INTO INV_DOC_FORMAT(DOC_CODE,DOC_NAME,PREFIX,SUFFIX,[LENGTH],[CURRENT]) VALUES('02','INV_UNIT_GROUP','UG','',5,0) </code></pre> <p>And, i'd have a fUNCTION OR Procedure but, i've an function here Which would generate the Document Number.</p> <pre><code>CREATE FUNCTION GET_DOC_FORMAT(@DOC_CODE VARCHAR(100))RETURNS VARCHAR(100) AS BEGIN DECLARE @PRE VARCHAR(10) DECLARE @SUF VARCHAR(10) DECLARE @LENTH INT DECLARE @CURRENT INT DECLARE @FORMAT VARCHAR(100) DECLARE @REPEAT VARCHAR(10) IF NOT EXISTS(SELECT DOC_CODE FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE) RETURN '' SELECT @PRE= PREFIX FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE SELECT @SUF= SUFFIX FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE SELECT @LENTH= [LENGTH] FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE SELECT @CURRENT= [CURRENT] FROM INV_DOC_FORMAT WHERE DOC_CODE=@DOC_CODE SET @REPEAT=REPLICATE('0',(@LENTH-LEN(CONVERT(VARCHAR, @CURRENT)))) SET @FORMAT=@PRE + @REPEAT +CONVERT(VARCHAR, @CURRENT+1) + @SUF RETURN @FORMAT END </code></pre> <p>You can use the Function like :</p> <pre><code>INSERT INTO INV_UNIT(UNIT_CODE,UNIT_NAME,UNIT_ALIAS,APPROVED,APPROVED_USER_ID,APPROVED_DATE) VALUES(DBO.GET_DOC_FORMAT('01'),@Unit_Name,@Unit_Alias,@APPROVED,@APPROVED_USER_ID,@APPROVED_DATE) --After Transaction Successfully complete, You can UPDATE INV_DOC_FORMAT SET [CURRENT]=[CURRENT]+1 WHERE DOC_CODE='01' </code></pre> <p>Or, you can create an Single Procedure which would handle all the things alone too.</p> <p>Hope you got the way...</p> <p>Hence, Looking at your Way, you are making an Mistake. You are getting SET @lastEmpID = ( SELECT TOP 1 Employee_ID FROM tblEmployee ORDER BY Employee_ID DESC )</p> <p>Last employee id, and then you are manipulating the rest of the ID. This would create or reuse the ID that was generated earlier however deleted now. Suppose EMP0010 was there. After some day that EMP has been Deleted. So, When you again create an Employeee next time, You gonna have Same Emp ID you had before for anohter Employe but no more exits however. I dont think thats a good idea. </p> <p>And, Instead of this :</p> <pre><code>DECLARE @NewEmployeeID as VARCHAR(6) IF @numEmp &lt; 10 SET @NewEmployee = SELECT 'EP000' + CONVERT(@EmpID) IF @numEmp &lt; 100 SET @NewEmployee = SELECT 'EP00' + CONVERT(@EmpID) IF @numEmp &lt; 1000 SET @NewEmployee = SELECT 'EP0' + CONVERT(@EmpID) IF @numEmp &gt;= 1000 SET @NewEmployee = SELECT 'EP' + CONVERT(@EmpID) </code></pre> <p>Which you used to repeat an Zero. You would use <strong>Replicate Function()</strong> of SQL. Like above on the Example of Mine.</p> <pre><code>SET @REPEAT=REPLICATE('0',(@LENTH-LEN(CONVERT(VARCHAR, @CURRENT)))) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POAuto increment Alphanumeric ID in MSSQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USNirmal Subedi
    1. USNirmal Subedi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThe table is already existing and i don't have the privilege to modify the structure. still thanks for the reply, this might be useful for future use.
      singulars
      1. PO
      1. USnigel
    2. COYou are creating an Whole Procedure That would produce an ID for an Just a SINGLE Transaction!!! Is this sounds good? Why don't you have an Procedure that would Generate an ID for All the Transactions that would have in your Application? If so then, now, You can suggest your Senior's there that way. Dont you! I think you have to. The way, you are doing, If you have 200 Types of Transactions like Employee, Like Agent, Supplier, Customer...What, Will you create Single Procedure foreach to generate an id? It would better you have an single procedure which could manage alllll. Suggest your Senior.
      singulars
      1. PO
      1. USNirmal Subedi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload