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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
User-defined functions are available only from SQL Server 2000 onwards.
ie NOT available with SQL server 7
what's your version ?
ie NOT available with SQL server 7
what's your version ?
ASKER
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.