469,621 Members | 1,688 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,621 developers. It's quick & easy.

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
Sep 6 '07 #1
2 2372
ck9663
2,878 Expert 2GB
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)
Sep 6 '07 #2
try:

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


Hey ck9663......... thanks a lot....... it really works............. thank you!!!!!!!!!!!
Sep 7 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by William C. White | last post: by
2 posts views Thread by Albert Ahtenberg | last post: by
3 posts views Thread by James | last post: by
reply views Thread by Ollivier Robert | last post: by
1 post views Thread by Richard Galli | last post: by
4 posts views Thread by Albert Ahtenberg | last post: by
1 post views Thread by inderjit S Gabrie | last post: by
2 posts views Thread by Jack | last post: by
3 posts views Thread by Sandwick | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.