Ms Sql

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

Quote:

Originally Posted by clear1140

Can anybody help me about this??

I'm new from using the MS SQL and i need a function/procedure/trigger......

there is this function from Delphi (FormatCurr) that I used to create a new record id
...FormatCurr('0000000000',Tqry.FieldByName('Budg etNo').AsInteger+1........

but then i want to make the same thing in MS SQL but i dont know what function to used so instead i did this dumb coding thing

CREATE PROCEDURE budget_newrecord
AS
DECLARE @.budgetno_old int,@.budgetno_new CHAR(10), @.budget_no int
SELECT TOP 1 @.budgetno_old = cast(budgetno as int)from budgetinfo order by budgetno desc

SET @.budget_no = @.budgetno_old + 1

IF @.budget_no <= 0
SET @.budgetno_new = '0000000001'
ELSE IF @.budget_no < 10
SET @.budgetno_new = '000000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100
SET @.budgetno_new = '00000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000
SET @.budgetno_new = '0000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000
SET @.budgetno_new = '000000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000
SET @.budgetno_new = '00000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000
SET @.budgetno_new = '0000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 10000000
SET @.budgetno_new = '000' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '00' + cast(@.budget_no as char)
ELSE IF @.budget_no < 100000000
SET @.budgetno_new = '0' + cast(@.budget_no as char)
ELSE IF @.budget_no < 1000000000
SET @.budgetno_new = cast(@.budget_no as char)

PRINT @.budgetno_new
RETURN @.budgetno_new
GO

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)|||

Quote:

Originally Posted by ck9663

try:

@.budgetno_new = right('000000000'+ rtrim(ltrim(cast(@.budget_no as varchar(12))),9)

Hey ck9663....... thanks a lot...... it really works.......... thank you!!!!!!!!!!!