How To Create Stored Procedure SQL To Store Number in database like 0001-0002-0010-0011 -9999

Question

1.00/5 (1 vote)

See more:

USE [RealEstate]
GO
/****** Object: StoredProcedure [dbo].[SP_AutoNumber] Script Date: 17/04/2014 12:00:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_AutoNumber]
@code int,
@MaxValue int,
@LastValue int ,
@strGUID uniqueidentifier

AS

(select @MaxValue = max(RealEstateNumber) from RlRegistrationRealEstate)

if @MaxValue is null set @MaxValue = '0001' set @LastValue = right(@MaxValue,3) + 1 return right('000' + convert(varchar(4),@lastValue),4) set @MaxValue= @MaxValue + @LastValue

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

Posted 19-Apr-14 2:32am

haythamsoft

Add a Solution

Comments

[no name] 19-Apr-14 8:41am

Posting and reposting the same bit of code over and over (this is the 5th time that I know of now) without bothering to ask a question or describe a problem is not going to get you anywhere. The only thing that it will get you is banned from the site for abusing the system.

3 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)

OriginalGriff · Answer 1 · 2014-04-19T04:17:00

Solution 1

Don't.
Store it as separate numbers and convert those to a string for presentation when you retrieve the information.

Posted 19-Apr-14 4:17am

OriginalGriff

Comments

haythamsoft 19-Apr-14 14:23pm

thanks i,m try my freind to change datatype to varchar(20) but not run correct stored correct if you can RE-write This Store to Store Number Like This 0001-0002-0003-0100 -1000-9999 THANKS

thatraja · Answer 2 · 2014-04-20T23:31:00

Solution 2

Custom Auto-Generated Sequences with SQL Server[^]

Customize it.

Posted 20-Apr-14 23:31pm

thatraja

Siva Hyderabad · Answer 3 · 2014-04-20T23:39:00

try this example...already i used this same requirement;

C#

ALTER PROCEDURE [dbo].[SP_AutoNumber]
@strGUID uniqueidentifier
AS
BEGIN
	
	SET NOCOUNT ON;
   
if exists(select * from TABLENAME where GUID = @strGUID)
begin
SELECT RIGHT(REPLICATE('0', 4) + CAST(CAST(COALESCE(MAX(columnname), 0) AS INT)  AS VARCHAR), 4) dcno
  FROM
(
  SELECT TOP 1 columnname[generatecolumn]
    FROM Tablename
   ORDER BY sno DESC
) q;
end
else



begin

SELECT RIGHT(REPLICATE('0', 4) + CAST(CAST(COALESCE(MAX(columnname), 0) AS INT) + 1 AS VARCHAR), 4) dcno
  FROM
(
  SELECT TOP 1 columnname[generatecolumn]
    FROM tablename
   ORDER BY sno DESC
) q;

end
END