SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sequence Number for Invoice?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 09/29/2012 :  23:24:20  Show Profile  Reply with Quote
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

Edited by - adbasanta on 09/29/2012 23:28:41

HenryFulmer
Posting Yak Master

USA
110 Posts

Posted - 09/30/2012 :  10:32:36  Show Profile  Reply with Quote
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))
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2166 Posts

Posted - 10/01/2012 :  06:59:24  Show Profile  Reply with Quote
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

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17682 Posts

Posted - 10/01/2012 :  08:19:00  Show Profile  Reply with Quote

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



KH
Time is always against us

Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 10/04/2012 :  10:47:16  Show Profile  Reply with Quote
Thank you HenryFulmer, DonAtWork, and khtan! It works!
,

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000