Link to home
Start Free TrialLog in
Avatar of Vasi04
Vasi04

asked on

convert SPs to user defined function

Please help me convert the result of my stored procedures into user defined function.

SP1--------------------------------------------------------------------------
create procedure to_base36(@s numeric, @result varchar(100) OUTPUT)
as
begin
if @s < 36
begin
if @s < 10
set @result = char(@s+48)
else
set @result = char(@s+55)
end
else
begin
declare @res1 varchar(100)
declare @res2 varchar(100)
declare @s1 numeric
declare @s2 numeric
set @s1 = @s/36
set @s2 = @s-floor(@s/36)*36
exec to_base36 @s1, @res1 OUTPUT
exec to_base36 @s2, @res2 OUTPUT
set @result = @res1 + @res2
end
end

----------SP2-----------------------
create procedure shortstamp(@d datetime, @hs numeric, @result varchar(100) OUTPUT)
as
begin
 
declare @res1 varchar(100)
declare @res2 varchar(100)
 
declare @num1 numeric
declare @num2 numeric
 
set @num1 = 466560 + (datepart(year,@d) - 1900) * 512 + datepart(month,@d) * 32 + datepart(day,@d)
exec to_base36 @num1, @res1 OUTPUT
 
set @num2 = datepart(hour,@d) * 524288 + datepart(minute, @d) * 8192 + datepart(second,@d) * 128 + @hs
exec to_base36 @num2 , @res2 OUTPUT
 
set @result = @res1 + @res2
end
--------------------
---------------------
I am calling these procs in my other SQL scripts to genearate @result value

declare @result varchar(100)
declare @hsecs numeric
declare @date datetime

set @hsecs = 1
set @date = getdate()
exec shortstamp @date, @hsecs, @result OUTPUT
-----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

It would be of great help.
ASKER CERTIFIED SOLUTION
Avatar of RichardCorrie
RichardCorrie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vasi04
Vasi04

ASKER

I am getting these errors:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Function'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 8
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 11
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 19
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 20
Must declare the variable '@s'.
Server: Msg 178, Level 15, State 1, Line 25
A RETURN statement with a return status can only be used in a stored procedure.
User-defined functions are available only from SQL Server 2000 onwards.
ie NOT available with SQL server 7
what's your version ?