Sequence Number for Invoice? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sequence Number for Invoice?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-09-29 : 23:24:20

Good day!

I want to generate a format like "00000043' from "42". Ive setup a counter table tbl_ID_Counter..


Table_Name   ID (where ID is bigint datatype)
PO-Summary   42

Here is my script but it still outputs 42.


ALTER PROCEDURE GetSequenceNumber
	-- Add the parameters for the stored procedure here
	@TransType AS VARCHAR(50),
	@return as varchar(10) output
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @SequenceNumber AS VARCHAR(10)
	SET NOCOUNT ON;

    SELECT @SequenceNumber=RIGHT(REPLICATE('0', 8) + CAST(ID AS VARCHAR(8)), 8)
    FROM tbl_ID_Counter WHERE TableName=@TransType
    
    SET @return=@SequenceNumber + 1
    
    RETURN @return
END
GO

It should output "00000043" if the ID is 42

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-30 : 10:32:36

maybe:


declare @id int
declare @return int
set @return=42
set @id=@return+1
select RIGHT(REPLICATE('0', 8),8)+ CAST(@ID AS VARCHAR(8))

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-01 : 06:59:24

Return command returns an integer. See BOL: http://msdn.microsoft.com/en-us/library/ms174998.aspx
Also, when you add a varchar and an integer, your varchar will get implicitly changed to an integer. Run this and see.

declare @SequenceNumber varchar(10)
SELECT @SequenceNumber=RIGHT(REPLICATE('0', 8) + '42', 8)
select @SequenceNumber
select @SequenceNumber + 1

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-01 : 08:19:00

[code]
ALTER PROCEDURE GetSequenceNumber
-- Add the parameters for the stored procedure here
@TransType AS VARCHAR(50),
@return AS VARCHAR(10) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @SequenceNumber table
(
ID int
)

SET NOCOUNT ON

UPDATE c
SET ID = ID + 1
OUTPUT INSERTED.ID
INTO @SequenceNumber (ID)
FROM tbl_ID_Counter c
WHERE TableName = @TransType

SELECT @return = right(replicate('0', 10) + convert(varchar(10), ID), 10)
FROM @SequenceNumber
END
GO

-- To Execute it
declare @return varchar(10)
exec GetSequenceNumber @TransType = 'A', @return = @return OUTPUT
select @return
[/code]

KH
[spoiler]Time is always against us[/spoiler]

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-10-04 : 10:47:16

Thank you HenryFulmer, DonAtWork, and khtan! It works!
,

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources