can i call a function within a function??
Printable View
can i call a function within a function??
Yes you can.Quote:
Originally Posted by mikee_phil
sql-server 2000 user define function... how??
Like this:
Shows this...Code:Create Function dbo.Func1 (@InVal int)
Returns int
As
Begin
Return (@InVal * 2)
End
Go
Create Function dbo.Func2 (@InVal int)
Returns int
As
Begin
Declare @TempVal int
Set @TempVal=dbo.Func1(@InVal)
Return (@TempVal * 100)
End
Go
Select dbo.Func1(10)
Select dbo.Func2(10)
Code:
-----------
20
(1 row(s) affected)
-----------
2000
(1 row(s) affected)
i am getting an error incorrect error near the keyword 'set'
by d way im creating the udf tha convert numeber in words
VB Code:
CREATE FUNCTION tens (@enter_tens int ) RETURNS varchar(20) AS BEGIN declare @tens varchar(20), @exec_function varchar(20) if left(@enter_tens,1)=1 begin set @tens = case @enter_tens when 11 then 'Eleven' when 12 then 'Twelve' when 13 then 'Thirteen' when 14 then 'Fourteen' when 15 then 'Fifteen' when 16 then 'Sixteen' when 17 then 'Seventeen' when 18 then 'Eighteen' when 19 then 'Nineteen' end end else if @enter_tens=20 begin set @tens='Twenty' end else [U][B]set @exec_function= dbo.ones(right(@enter_tens,1)[/B][/U] --te error is in this lines set @tens= case when left(@enter_tens,1)= 2 then 'Twenty'+' ' + @exec_function when left(@enter_tens,1)=3 then 'Thirty'+' ' +@exec_function when left(@enter_tens,1)=4 then 'Forty'+' ' +@exec_function when left(@enter_tens,1)=5 then 'Fifty'+' ' +@exec_function when left(@enter_tens,1)=6 then 'Sixty'+' ' +@exec_function when left(@enter_tens,1)=7 then 'Seventy'+' ' +@exec_function when left(@enter_tens,1)=8 then 'Eighty'+' ' +@exec_function when left(@enter_tens,1)=9 then 'Ninety'+' ' +@exec_function end return(@tens) ENDVB Code:
CREATE FUNCTION ones (@enter_ones int) RETURNS nchar(10) AS BEGIN declare @ones nchar(10) set @ones= case @enter_ones when 0 then 'k' when 1 then 'One' when 2 then 'Two' when 3 then 'Three' when 4 then 'Four' when 5 then 'Five' when 6 then 'Six' when 7 then 'Seven' when 8 then 'Eight' when 9 then 'Nine' end return(@ones) ENDVB Code:
CREATE FUNCTION number_to_words (@enter_no float) RETURNS nchar(300) AS BEGIN declare @len_number int, @trim_number float,@converted nchar(300) set @trim_number= round(@enter_no,0) set @len_number=len(@enter_no) set @converted='k' --loop_till_len_zero: if @len_number=9 begin set @converted='mike' end --not yet finnished --not yet finnished --not yet finnished return(@converted) END
Seems to me like you are missing a closing parenthesis
set @exec_function= dbo.ones(right(@enter_tens,1) --te error is in this lines
should be
set @exec_function= dbo.ones(right(@enter_tens,1)) --te error is in this lines
btw - good luck! if you are so inclined post your complete function here - we would all love to see it...
here's what ive come up
Quote:
CREATE FUNCTION ones (@enter_ones int)
RETURNS nchar(10) AS
BEGIN
declare @ones varchar(10)
set @ones= case @enter_ones
when 0 then 'k'
when 1 then 'One '
when 2 then ' Two '
when 3 then ' Three '
when 4 then ' Four '
when 5 then ' Five '
when 6 then 'Six '
when 7 then ' Seven '
when 8 then ' Eight '
when 9 then ' Nine '
end
return (@ones)
Quote:
CREATE FUNCTION tens (@enter_tens int )
RETURNS varchar(20) AS
BEGIN
declare @tens varchar(20), @exec_function varchar(20)
if @enter_tens=0 -- for 0 e.g 100
begin
set @tens ='k'
end
if (@enter_tens >=1 )and (@enter_tens<=9) -- for below 9 e.g 205
begin
set @tens=dbo.ones(right(@enter_tens,1))
end
if (@enter_tens >=10 )and (@enter_tens<=19) --for below 19 e.g 113
begin
set @tens = case @enter_tens
when 10 then 'Ten '
when 11 then 'Eleven'
when 12 then 'Twelve'
when 13 then 'Thirteen'
when 14 then 'Fourteen'
when 15 then 'Fifteen'
when 16 then 'Sixteen'
when 17 then 'Seventeen'
when 18 then 'Eighteen'
when 19 then 'Nineteen'
end
end
if (@enter_tens >19) -- for below 100 e.g 187
begin
set @exec_function= ltrim(dbo.ones(right(@enter_tens,1)))
set @tens= case
when left(@enter_tens,1)= 2 then 'Twenty'+' ' + @exec_function
when left(@enter_tens,1)=3 then 'Thirty'+' ' +@exec_function
when left(@enter_tens,1)=4 then 'Forty'+' ' +@exec_function
when left(@enter_tens,1)=5 then 'Fifty'+' ' +@exec_function
when left(@enter_tens,1)=6 then 'Sixty'+' ' +@exec_function
when left(@enter_tens,1)=7 then 'Seventy'+' ' +@exec_function
when left(@enter_tens,1)=8 then 'Eighty'+' ' +@exec_function
when left(@enter_tens,1)=9 then 'Ninety'+' ' +@exec_function
end
end
return (@tens)
END
Quote:
CREATE FUNCTION hundreds (@enter_hundreds int)
RETURNS varchar (15) AS
BEGIN
declare @hundreds varchar (17)
set @hundreds= case left(@enter_hundreds,1)
when 0 then 'k'
else
rtrim(dbo.ones(left(@enter_hundreds,1)))+' Hundred '
end
return (@hundreds)
end
up to 999 million onlyQuote:
CREATE FUNCTION number_to_words (@enter_no numeric)
RETURNS varchar(300) AS
BEGIN
declare @len_number int, @trim_number float,@converted varchar(300)
set @trim_number= round(ltrim(rtrim(@enter_no)),0)
set @len_number=len(@enter_no)
set @converted='k'
if @len_number=1
begin
if @trim_number = 0
--begin
set @converted= 'Zero'
--end
else
set @converted=ltrim(rtrim(dbo.ones(@enter_no)))
end
if @len_number=2
begin
set @converted= rtrim(ltrim(dbo.tens(@enter_no)))
end
if @len_number=3
begin
set @converted=replace( ltrim(dbo.hundreds(left(@enter_no,1)))+ rtrim(dbo.tens(right(@enter_no,2))),'k','')
end
if @len_number=4
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,2),1)))+rtrim(dbo.tens(right(right(@enter_no,3),2))) +@converted,'k','')
set @converted= replace(ltrim(rtrim(dbo.ones((left(@enter_no,1)))))+' Thousand '+@converted,'k','')
end
if @len_number=5
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,4),1)))+rtrim(dbo.tens(right(right(@enter_no,3),2))) +@converted,'k','')
set @converted= replace(ltrim(rtrim(dbo.tens((left(@enter_no,2)))))+' Thousand '+@converted,'k','')
end
if @len_number=6
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,4),1)))+rtrim(dbo.tens(right(right(@enter_no,3),2))) +@converted,'k','')
set @converted= replace(ltrim(dbo.hundreds(left(@enter_no,1)))+rtrim(dbo.tens(right(left(@enter_no,3),2)))+' Thousand '+@converted,'k','')
end
if @len_number=7
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,5),1)))+rtrim(dbo.tens(right(right(@enter_no,3),2))) +@converted,'k','')
if right(left(@enter_no,6),3)='000'
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,2),1)))+rtrim(dbo.tens(right(left(@enter_no,4),2)))+ @converted,'k','')
end
else
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,2),1)))+rtrim(dbo.tens(right(left(@enter_no,4),2)))+ ' Thousand '+@converted,'k','')
end
set @converted= replace(ltrim(rtrim(dbo.ones(left(@enter_no,1))))+' Million '+@converted,'k','')
end
if @len_number=8
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,6),1)))+rtrim(dbo.tens(right(right(@enter_no,3),2))) +@converted,'k','')
if right(left(@enter_no,6),3)='000'
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,3),1)))+rtrim(dbo.tens(right(left(@enter_no,6),2)))+ @converted,'k','')
end
else
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,3),1)))+rtrim(dbo.tens(right(left(@enter_no,5),2)))+ ' Thousand '+@converted,'k','')
end
set @converted= replace(ltrim(rtrim(dbo.tens(right(left(@enter_no,2),2))))+' Million '+@converted,'k','')
end
if @len_number=9
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,7),1)))+rtrim(dbo.tens(right(right(@enter_no,3),2))) +@converted,'k','')
if right(left(@enter_no,6),3)='000'
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,4),1)))+rtrim(dbo.tens(right(left(@enter_no,6),2)))+ @converted,'k','')
end
else
begin
set @converted= replace(ltrim(dbo.hundreds(right(left(@enter_no,4),1)))+rtrim(dbo.tens(right(left(@enter_no,6),2)))+ ' Thousand '+@converted,'k','')
end
set @converted= replace(ltrim(dbo.hundreds(left(@enter_no,1)))+rtrim(dbo.tens(right(left(@enter_no,3),2)))+' Million '+@converted,'k','')
end
return( rtrim(@converted))
END
select dbo.number_to_words('655003441')