user define function

[mikee_phil]

can i call a function within a function??

[kaffenils]

can i call a function within a function??

Yes you can.

[mikee_phil]

sql-server 2000 user define function... how??

[szlamany]

Like this:

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)

Shows this...

            
----------- 
20

(1 row(s) affected)

            
----------- 
2000

(1 row(s) affected)

[mikee_phil]

i am getting an error incorrect error near the keyword 'set'
by d way im creating the udf tha convert numeber in words

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)
END

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)
 
 
 
 
END

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

[szlamany]

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...

[mikee_phil]

here's what ive come up

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)

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

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

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

up to 999 million only
select dbo.number_to_words('655003441')