?
Solved

convert SPs to user defined function

Posted on 2004-11-17
3
Medium Priority
?
317 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Vasi04
3 Comments
 
LVL 10

Accepted Solution

by:
RichardCorrie earned 2000 total points
ID: 12603530
1)
create Function dbo.Fn_to_base36(@s numeric)
returns varchar(100)
as
begin
     declare @result varchar(100)
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
return @Result
end
-----------------------
2)
create function dbo.fn_shortstamp(@d datetime, @hs numeric)
returns  varchar(100)
as
begin
declare  @result varchar(100)
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
return @result
end
---------------------------------------------
Use
declare @result varchar(100)
declare @hsecs numeric
declare @date datetime

set @hsecs = 1
set @date = getdate()
select @result =  dbo.fn_shortstamp(@date, @hsecs)

-- note when using/calling a function you must use the Owner qualifier (ie owner.function)

/Richard

0
 

Author Comment

by:Vasi04
ID: 12603613
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.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12603739
User-defined functions are available only from SQL Server 2000 onwards.
ie NOT available with SQL server 7
what's your version ?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question