How to translate Oracle package to SQL Server?

Question

In Oracle I have lots of stored procedures using a package which basically stores (encapsulates) and initializes all variables used by these procedures. There is one function in the package as well which takes care of initializing all it's package variables.

My question is: how to port this to SQL Server?

My first attempt is to declare all package variables and use them as OUTPUT parameters for a procedure to initialize them, but then I need to declare these variables over and over again in each procedure using them (and there are a lots of them in the package). Is there any better (and DRY) way to do this on SQL Server?

Some code to explain it:

ORACLE:

The package:

create or replace 
PACKAGE MYPARAMS AS 

  /**
  param container 
  */
  type rc_params is record
  (
    var1 varchar2(30),
    var2 integer
  );
  /**
  init param container
  use: v_params rc_pkp_plan_params := MYPARAMS.f_get_params(initvar)
  */
  function f_get_params(initvar number) return rc_params;

END MYPARAMS;
/

The package body:

CREATE OR REPLACE
PACKAGE BODY MYPARAMS AS

  function f_get_params(initvar number) return rc_params AS
    retval rc_params;
  BEGIN
    retval.var1 := 'MY_VAR1';
    retval.var2 := initvar;
    return retval;
  END f_get_params;

END MYPARAMS;
/

Some usage example:

declare
  initvar integer := 22;
  v_params MYPARAMS.rc_params := MYPARAMS.f_get_params(initvar);
begin
  dbms_output.put_line(v_params.var1 || ' initialized by ' || v_params.var2);
end;

SQL Server:

if exists (select * from sysobjects where id = object_id('f_get_params'))
  drop procedure f_get_params
go
create  procedure  f_get_params(
    @initvalue integer,
    @var1 varchar(30) OUTPUT,
    @var2 integer OUTPUT
)
as
    set @var1 = 'MYVAR1'
    set @var2 = @initvalue
go  

-- this block i would like to avoid:
declare 
    @initvalue integer = 33,
    @var1 varchar(30),
    @var2 integer

exec f_get_params @initvalue, @var1 OUTPUT, @var2 OUTPUT
print @var1 + ' initialized by ' + convert(varchar(2), @var2)

Hope the description is clear enough...

You don't like the declare @initvalue integer = 33, var1 varchar(30), @var2 integer part, specifically? — tommy_o
yes, because i need those vars in almost all my procedures. therefore i want to encapsulate these vars and (most important for me) their initialization. — Jakob

vr8ce vr8ce · Accepted Answer · 2013-08-07T23:22:40

Unfortunately, T-SQL doesn't have anything like Oracle's packages, package variables, or structures of variables. Oh that it did.

What you've done is probably the easiest way to accomplish it in T-SQL, even if it does require duplicating the variables.

You can use a # table, i.e. create a #params table in f_get_parms that contains all of the variables, then use that # table in all of the other procs to retrieve them. The downside is that you still either have to declare the variables in the calling procedures, or use DML to access the columns in the # table, which is a lot more cumbersome than having them as variables.

Another way I've used before is to use XML to pass multiple variables around but treat them as a single one. It's still more cumbersome to access the values than in variables, but it has the advantage of allowing you to use a function instead of a procedure to get the values.

CREATE FUNCTION dbo.uf_get_params (
  @initvar int
)
RETURNS xml
AS
BEGIN
  DECLARE @xml xml,
          @var1 varchar(30) = 'MYVAR'    -- setting value on DECLARE requires SQL2008+

  SELECT @xml = (SELECT @var1 AS var1,
                        @initvar AS initvar
                    FOR XML RAW('params'))

  RETURN @xml
END
go

In your calling procedure, you would have

DECLARE @params xml = (SELECT dbo.uf_get_parms(12))

to get the parameters, then use standard XML/XQUERY functions to retrieve the individual variables (attributes) from the @params XML variable.

How to translate Oracle package to SQL Server?

1 Answers