"It doesn't matter if you win or lose, as long as you look cool doing it." - Julio Scoundrél - Order of the Stick webcomic.
I only recently became aware of T-SQL Tuesday and considering my last post on Joins, if I had known about it, that post would have made a good submission for last month's T-SQL Tuesday. However, that aside, it's not last month, it's this month.
Brad Schulz (blog) is hosting this month's party and his topic of choice is "Prox 'n' Funx" (Procedures and Functions).
So here we go.
Anyone who spent any time learning anything about just about any kind of programming language (there are exceptions there too) should have learned early on about the usefulness of procedures and functions. The Wikipedia entry on this topic defines it as follows: "a subroutine (also called procedure, function, routine, method, or subprogram) is a portion of code within a larger program that performs a specific task and is relatively independent of the remaining code". This definition holds true in SQL Server - however instead of being a part of a larger program, they are stored in the database as executable pieces of code - and these can be called by any other T-SQL program in the database - indeed you can even call them across databases (let's face it, the majority of system procedures are stored in a system database).
With Programming 101 out of the way, I thought I'd write a little about some of the differences between Oracle and SQL Server in Procedure and Function definition for those people (like me), who have to work in both database engines. Since I recently had to port some PL/SQL code to T-SQL, it seems like a good time to write about it.
The differences aren't so much in how the procedure is defined as much as how you call it.
The basic construct for both languages is the same:
CREATE [or replace] PROCEDURE schema.procname(parameters)
AS
The schema part of the declaration is optional. In SQL Server it defaults to the default schema (usually dbo) and in Oracle it's the user name you're logged in as. (If you're not familiar with Oracle's security model you'll have to trust me, that's a post for another time and I won't go into it here). Oracle also allows the "OR REPLACE" syntax to overlay an existing stored procedure - as opposed to SQL Server where you need to drop an object before recreating it (or you can use "alter procedure" to replace the procedure so that permissions on the object are not lost).
Definition of the parameters varies only slightly.
For Oracle, list the parameter names and types, each declaration separated by a comma:
CREATE PROCEDURE myproc1 (param1 char(10), param2 integer, param3 date)
AS
The SQL Server syntax is almost exactly the same, except you need to prefix the parameter names with the "@" sign.
CREATE PROCEDURE myproc1 (@param1 char(10), @param2 int, @param3 datetime)
AS
The function definitions are also very close. The syntax for the returning of the function value differs only slightly. In Oracle it looks like this:
CREATE FUNCTION myfunc1 (param1 date) RETURN DATE
AS
The function definitions in SQL Server looks like this:
CREATE FUNCTION myfunc1 (@param1 datetime) RETURNS DATETIME
AS
The big difference between the two is how they are called. This was something that tricked me up the first time I tried converting code between the two. Both expect a comma separated parameter list, however, Oracle expects the parameters to be enclosed in parentheses, this SQL Server does not. What we come down to, is this for Oracle: The function definitions are also very close. The syntax for the returning of the function value differs only slightly. In Oracle it looks like this:
EXEC myproc1 (param1, param2, param3);
while in SQL Server it looks like this:
EXEC myproc1 @param1, @param2, @param3;
This all gets a little murkier when you realize that in SQL Server user functions are called differently. A user function has to be called using 2 part names (you have to call it as dbo.myfunc and not just myfunc) *and* parameters are passed in parentheses, not as strings passed after a procedure call. This is yet another "gotcha" for the cross-database person.
Example time. In SQL Server it looks like this.
create procedure dbo.test1 (@date1 datetime, @days1 int)
as
declare @result1 datetime;
select @result1 = dateadd(dd, @days1, @date1);
print 'Input : ' + convert(varchar(10), @date1, 101);
print 'Output: ' + convert(varchar(10), @result1, 101);
go
Command(s) completed successfully.
declare @indate1 datetime;
select @indate1=getdate();
exec dbo.test1 @indate1,2
Input : 11/07/2011
Output: 11/09/2011
And the same kind of thing in Oracle:
create or replace procedure test1 (date1 date, days1 integer)
as
result1 date;
begin
result1 := date1 + days1;
dbms_output.put_line('Input : ' || to_char(date1, 'MM/DD/YYYY'));
dbms_output.put_line('Output: ' || to_char(date1, 'MM/DD/YYYY'));
end;
/
Procedure created.
SQL> set serveroutput on
SQL> exec test1(sysdate, 2);
Input : 11/07/2011
Output: 11/09/2011
PL/SQL procedure successfully completed.
SQL>
The result are the same, but there's the little syntactical tricks that are needed in order to make it work.
Finally, same treatment for similar functions. SQL Server first.
create function dbo.func1 (@date1 datetime, @days1 int)
returns datetime
as
begin
declare @result1 datetime;
select @result1 = dateadd(dd, @days1, @date1);
RETURN @result1
end
GO
Command(s) completed successfully.
declare @indate1 datetime;
declare @outdate1 datetime;
select @indate1=getdate();
select @outdate1=dbo.func1(@indate1, 3)
print 'Input : ' + convert(varchar(10), @indate1, 101);
print 'Output: ' + convert(varchar(10), @outdate1, 101);
Input : 11/07/2011
Output: 11/10/2011
As noted above, if you replace "dbo.func1" with "func1", you get this error message:
Msg 195, Level 15, State 10, Line 4 'func1' is not a recognized built-in function name.
And finally, for Oracle:
create or replace function func1 (date1 date, days1 integer)
return date
as
result1 date;
begin
result1 := date1 + days1;
return result1;
end;
/
Function created.
SQL> select 'Input Date = '||sysdate||' / Output Date = '||func1(sysdate, 3)
2 as Input_Output_Date from dual;
INPUT_OUTPUT_DATE
------------------------------------------------
Input Date = 07-NOV-11 / Output Date = 10-NOV-11
SQL>
When working in two different databases, it's important to keep in mind the differences in syntax between them when working on whatever solution you are working on. Little things like a missing pair of parentheses, or missing @'s can cause you just enough irritation to make you want to pull out what in my case, is the very little hair left on your head. The bottom line, especially for people like me who walk both sides of the Oracle / SQL Server line is to make sure you understand the subtle differences in the syntax you're working with.
As an "end of post" bonus, just because we're talking about functions, there's something else I'd like to throw out there. Here's a post I wrote in September and how a compatibility setting can "break" a Dynamic Management Function - sys.dm_db_index_physical_stats is broken !?
