How to translate Oracle package to SQL Server? -

in oracle have lots of stored procedures using package stores (encapsulates) , initializes variables used these procedures. there 1 function in package takes care of initializing it's package variables.

my question is: how port sql server?

my first attempt declare package variables , use them output parameters procedure initialize them, need declare these variables on , on again in each procedure using them (and there lots of them in package). there better (and dry) way on sql server?

some code explain it:

oracle:

the package:

create or replace  package myparams     /**   param container    */   type rc_params 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    function f_get_params(initvar number) return rc_params     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 ' || v_params.var2); end; 

sql server:

if exists (select * sysobjects 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 )     set @var1 = 'myvar1'     set @var2 = @initvalue go    -- block avoid: declare      @initvalue integer = 33,     @var1 varchar(30),     @var2 integer  exec f_get_params @initvalue, @var1 output, @var2 output print @var1 + ' initialized ' + convert(varchar(2), @var2) 

hope description clear enough...

unfortunately, t-sql doesn't have oracle's packages, package variables, or structures of variables. oh did.

what you've done easiest way accomplish in t-sql, if require duplicating variables.

you can use # table, i.e. create #params table in f_get_parms contains of variables, use # table in of other procs retrieve them. downside still either have declare variables in calling procedures, or use dml access columns in # table, lot more cumbersome having them variables.

another way i've used before use xml pass multiple variables around treat them single one. it's still more cumbersome access values in variables, has advantage of allowing use function instead of procedure values.

create function dbo.uf_get_params (   @initvar int ) returns xml begin   declare @xml xml,           @var1 varchar(30) = 'myvar'    -- setting value on declare requires sql2008+    select @xml = (select @var1 var1,                         @initvar initvar                     xml raw('params'))    return @xml end go 

in calling procedure, have

declare @params xml = (select dbo.uf_get_parms(12)) 

to parameters, use standard xml/xquery functions retrieve individual variables (attributes) @params xml variable.