Can have this code in SQL Server?

I need to adjust this code to run on SQL server. P

create or replace function to_base36(s number)
return varchar2 as
begin
 if s < 36 then
  if s < 10 then
   return chr(s+48);
  else
   return chr(s+55);
  end if;
 else
  return(concat(to_base36(floor(s/36)), to_base36(s-floor(s/36)*36)));
 end if;
end;
/

create or replace function shortstamp(d date, hs number)
return varchar2 as
begin
 return concat(to_base36(466560 + (extract(year from d) - 1900) * 512 + extract(month from d) * 32 +
  extract(day from d)), to_base36(extract(hour from d) * 524288 + extract(minute from d) * 8192 +
  extract(second from d) * 128 + hs));
end;
/

hsecs := hsecs + 1;
record_id := shortstamp(sysdate, hsecs);
Vasi04Asked:
Who is Participating?
 
BillAn1Connect With a Mentor Commented:
here is the code in stored procedure format, plus an example of how to call them....

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


GO
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


GO

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

set @hsecs = 1
set @date = getdate()
exec shortstamp @date, @hsecs, @result OUTPUT
select @result
0
 
Vasi04Author Commented:
This code was written for Oracle. I need to run this code on SQL Server.
When the run above code in QA, I get these errors:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'or'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'then'.
Server: Msg 195, Level 15, State 1, Line 6
'chr' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 8
'chr' is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 11
'to_base36' is not a recognized function name.
Server: Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'from'.
0
 
Vasi04Author Commented:
Bill, can you help me on this :-)
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
imrancsConnect With a Mentor Commented:
create function dbo.to_base36(@s numeric)
returns varchar(100) as
begin
 if @s < 36
 begin
  if @s < 10
   return char(@s+48)
  else
   return char(@s+55)
  end
 else
  return (Cast(dbo.to_base36(floor(@s/36)) as varchar)+ cast(dbo.to_base36(@s-floor(@s/36)*36)as varchar))

return '0'
end



create function dbo.shortstamp(@d datetime, @hs numeric)
returns varchar(100) as
begin
 return       cast(dbo.to_base36(466560 + (datepart(year,@d) - 1900) * 512 + datepart(month,@d) * 32 +
                    datepart(day,@d)) as varchar) +
                  cast(dbo.to_base36(datepart(hour,@d) * 524288 + datepart(minute, @d) * 8192 +
                    datepart(second,@d) * 128 + @hs) as varchar)
end
0
 
imrancsCommented:
if you dont want to ignore the decimle points in the calculations than please change numeric to numeric(18,2)

Imran
0
 
Vasi04Author Commented:
Thanks so much Imran

I get these errors in my QA:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@s'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Server: Msg 178, Level 15, State 1, Line 14
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '('.

Any idea?
0
 
Vasi04Author Commented:
I want to set values to these variables so that I can use these varibales in my SP

hsecs := hsecs + 1;
record_id := shortstamp(sysdate, hsecs);

Is this correct?
@hsecs = hsecs + 1;
@record_id = shortstamp(sysdate, hsecs);
0
 
BillAn1Commented:
to creat the functions above, just seperate them with a GO, or else run the two pieces of code seperately.
the correct syntax for your variables is

@hsecs = @hsecs + 1
@record_id = dbo.shortstamp(getdate(), @hsecs)
0
 
Vasi04Author Commented:
Thanks Bill. Nice to hera from you again
I had posted my oracle code above, wnated same code to run on my SQL server.  I get syntax errors with code suggested my Imran. I think there are minor changes.
Please help
0
 
Vasi04Author Commented:
Please help. I want have this code iny my SP
-----
Go
create function dbo.to_base36(@s numeric)
returns varchar(100) as
begin
 if @s < 36
 begin
  if @s < 10
   return char(@s+48)
  else
   return char(@s+55)
  end
 else
  return (Cast(dbo.to_base36(floor(@s/36)) as varchar)+ cast(dbo.to_base36(@s-floor(@s/36)*36)as varchar))

return 0
end

GO
create function dbo.shortstamp(@d datetime, @hs numeric)
returns varchar(100) as
begin
 return      cast(dbo.to_base36(466560 + (datepart(year,@d) - 1900) * 512 + datepart(month,@d) * 32 +
                 datepart(day,@d)) as varchar) +
               cast(dbo.to_base36(datepart(hour,@d) * 524288 + datepart(minute, @d) * 8192 +
                 datepart(second,@d) * 128 + @hs) as varchar)
end

@hsecs = @hsecs + 1
@record_id = dbo.shortstamp(getdate(), @hsecs)
---
I get these errors when I parse this my QA

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@s'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@s'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '('.
Server: Msg 178, Level 15, State 1, Line 14
A RETURN statement with a return status can only be used in a stored procedure.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '('.
0
 
BillAn1Commented:
Vasi,
we're back to the old problem again - you are using SQLServer 7.0 right?
SQL7 doesn't suppport functions. You will need to do this another way - e.g. create then as procedures.
If you already have a sotred proc, that you want to call these functions, perhaps you can just put the code into the stored proc?
0
 
Vasi04Author Commented:
Thanks bill
yes sql 7.0
Pls help me to create procedures with above codes

Then pls advice how to call them as functions from another sp or trigger
0
 
Vasi04Author Commented:
Thanks Bill, you are a darling!!!
0
 
BillAn1Commented:
I think you should give credit to imrancs too, he did translate the Oracle to SQLServer for you !! - can you split points?
0
 
imrancsCommented:
thanks BillAn1 for you kind favour, I really appriciate it. It doesn't matter that I have credited some points or not, I am just glad that Vasi04 has got his problem solved.

Thanks again. :o)


Imran
0
 
Vasi04Author Commented:
Sorry Imran, will try to split the points.
Actually I was expecting your comments on error msgs which I posted twice. There was no follow-up for a long time. I was wondering whether to give up on this and develop a diff solution, instead of copying logic from oracle code. But for Bill's answer.
Thanks again. I have intentions of hurting you. :-)
Please advice me how could I split.
0
 
BillAn1Commented:
I think you mean "I have NO intentions of hurting you. :-)"  !!!
I think now that points have been assigned, you need to post a Q in the support area (0 points) asking the moderator to re-open Q, then you can split again.
0
 
Vasi04Author Commented:
Oops!! man....... I am somewhere else. May be I am stressed out by correcting and reprogramming SQL stuff!
Imran if you are able to read this post, I will be happy.
I HAVE NO INTENTIONS OF HURTING YOU!!! :-))


0
 
imrancsCommented:
Its okey man.


Imran
0
 
imrancsCommented:
hmmm,

Vasi04 I think you really take it serious :o) , any way I really appreiciate it.


Imran
0
 
imrancsCommented:
thanks Vasi04 and BillAn1 too.


Imran
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.