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',Tq ry.FieldByName( 'BudgetNo').AsI nteger+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_newrecor d
AS
DECLARE @budgetno_old int,@budgetno_n ew 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
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',Tq ry.FieldByName( 'BudgetNo').AsI nteger+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_newrecor d
AS
DECLARE @budgetno_old int,@budgetno_n ew 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
Comment