How to Create Stored Procedure Auto Generate Number 0001-9999 store in the table varchar()

Question

1.00/5 (3 votes)

See more:

SQL-Server-2012

SQL

USE [RealEstate]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[generate]

 as

  Declare @MaxValue nvarchar(20)
  Declare @LastValue nvarchar(20)
  Declare @strGUID uniqueidentifier

select @MaxValue = max(RealEstateNumber) from RlRegistrationRealEstate

if @MaxValue is null set @MaxValue = '0001'
else
set @MaxValue =convert(int,right(@MaxValue,4)) + 1


  set @MaxValue = @MaxValue + @LastValue

UPDATE [dbo].RlRegistrationRealEstate SET RealEstateNumber=@MaxValue WHERE GUID =@strGUID

when you insert new record in the dataBase Display Number Of Record Like this 0002-0003-9999

Posted 20-Apr-14 10:03am

haythamsoft

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

karthik Udhayakumar · Answer 1 · 2014-04-20T13:09:00

Solution 1

Hello Hay,
pls refer this
http://www.w3schools.com/sql/sql_autoincrement.asp[^]

All the best:)

Posted 20-Apr-14 13:09pm

karthik Udhayakumar

Andrius Leonavicius · Answer 2 · 2014-04-20T13:50:00

Hi,

First of all, let me say that I just don't understand a practical reason for doing this. This may cause you some problems in the future. Are you sure that you need this?

Have you considered storing your values as an auto incremented integer instead of varchar (and maybe using "0001-9999" format for display purposes only)?

Anyway, I am going to show you how this could be done.

Here's my sample:

SQL

DECLARE @Table TABLE ([Value] VARCHAR(4));
DECLARE @NewValue INT;
 
INSERT INTO @Table ([Value])
VALUES ('0001'), ('0009');
 
SET @NewValue = (SELECT ISNULL(MAX(CONVERT(INT, [Value])), 0) + 1 FROM @Table);
 
INSERT INTO @Table ([Value])
VALUES (REPLICATE('0', (4 - LEN(@NewValue))) + CONVERT(VARCHAR(4), @NewValue));

Note: Only numeric values should be stored in the column "Value".
Given values are '0001' and '0009'. As you can see, I am getting a new value as integer. Then I am using the REPLICATE ( string_expression ,integer_expression ) function to repeat '0' value specified number of times.

There should be three records in the table: '0001', '0009' and newly generated '0010'. Let's check it:

SQL

SELECT * FROM @Table;

XML

Value
0001
0009
0010

Your stored procedure could look something like this:

SQL

USE [RealEstate]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[generate]
AS
  BEGIN
      DECLARE @NewValue INT;
      DECLARE @strGUID UNIQUEIDENTIFIER;
 
      SET @NewValue = (SELECT ISNULL(MAX(CONVERT(INT, [RealEstateNumber])), 0) + 1 FROM [dbo].[RlRegistrationRealEstate]);
 
      UPDATE [dbo].[RlRegistrationRealEstate]
      SET [RealEstateNumber] = (REPLICATE('0', (4 - LEN(@NewValue))) + CONVERT(VARCHAR(4), @NewValue))
      WHERE GUID = @strGUID;
  END

By the way, you're not setting any value for @strGUID variable!

-------------------------------------------------------------------------
Addendum:
In SQL Server 2012 you can use the FORMAT function instead of the REPLICATE.
Here's the simple example:

SQL

DECLARE @NewTable TABLE ([intValue] INT);

INSERT INTO @NewTable ([intValue])
VALUES (1), (9);

SELECT FORMAT ([intValue], '0000') AS [varcharValue] FROM @NewTable;

XML

varcharValue
0001
0009