T-SQL: procedures

Named parameters

create procedure tq84_named_params (
   @param_one  integer,
   @param_two  varchar(10)
)
as
   print('param_one is: ' + str(@param_one));
   print('param_two is: ' +     @param_two );
go

exec tq84_named_params
        @param_one = 42,
        @param_two = 'Forty-two';

drop procedure tq84_named_params;

Github repository about-MSSQL, path: /t-sql/procedures/named-parameters.sql

Declare variables

create procedure tq84_declare_variables(@num integer) as
declare
   @num_times_2  integer,
   @num_squared  integer;

   select
      @num_times_2 = @num * 2,
      @num_squared = @num * @num;

   print str(@num) + ' * 2 = ' + str(@num_times_2);
   print str(@num) + '^2   = ' + str(@num_squared);

go

exec tq84_declare_variables 42;

drop procedure tq84_declare_variables;

Github repository about-MSSQL, path: /t-sql/procedures/declare-variables.sql

Calling a procedure from within a procedure

create procedure tq84_callee(@txt as varchar(10)) as
   print 'callee says: ' + @txt;
go

create procedure tq84_caller(@txt as varchar(10)) as
   print 'caller says: ' + @txt;
   execute tq84_callee 'bar';
go

exec tq84_caller 'foo';

drop procedure tq84_caller;
drop procedure tq84_callee;
go

Github repository about-MSSQL, path: /t-sql/procedures/call-proc-from-proc.sql

Output parameters

Apparently, a user defined function does not allow to execute DML statements. Thus, in order to return a value from a »function« that modifies data in the database, an output parameter might be used.

The parameter needs to be declared with the output keyword when the procedure is declared as well as when it is executed:

if object_id('dbo.tq84_tab', 'u') is not null
   drop table dbo.tq84_tab;
go
 
create table dbo.tq84_tab (
   id  integer     identity,
   txt varchar(10) not null
);
go
 
 
if object_id('dbo.tq84_ins', 'p') is not null
   drop procedure dbo.tq84_ins;
go
 
create procedure dbo.tq84_ins
   @txt varchar(10),
   @id  integer output
as
   set nocount on;
 
   insert into tq84_tab (txt) values (@txt);
 
   set @id = @@identity;
go
 
declare
   @id_foo integer,
   @id_bar integer,
   @id_baz integer;
 
--
-- Note: the keyword output needs to be stated
-- even when the procedure is executed...
-- 
   exec dbo.tq84_ins 'foo', @id_foo output;
   exec dbo.tq84_ins 'bar', @id_bar output;
   exec dbo.tq84_ins 'baz', @id_baz output;
 
go
 
select * from tq84_tab;

Github repository about-MSSQL, path: /t-sql/procedures/output-parameters.sql

Optional parameters

If a parameter is declared with an assignment (@param = 42), the parameter has a default value (here: 42) and thus becomes optional: if not explicitly given a value when the procedure is invoked, the value of the parameter will then be that of the default value:

create or alter procedure tq84_some_optional_params (
  @req_one  integer,
  @req_two  varchar(10),
  @opt_one  integer      = 42,
  @opt_two  varchar(20)  ='Hello world'
)
as
  print('req_one = ' + isNull(str(@req_one), 'n/a'));
  print('req_two = ' + isNull(    @req_two , 'n/a'));
  print('opt_one = ' + isNull(str(@opt_one), 'n/a'));
  print('opt_two = ' + isNull(    @opt_two , 'n/a'));
  print('');
go

exec tq84_some_optional_params 1, 'one' ;
exec tq84_some_optional_params 2, 'two' , 3, 'three';
exec tq84_some_optional_params 4, 'four', @opt_two = 'good bye';
go

Github repository about-MSSQL, path: /t-sql/procedures/optional-parameters.sql

Settings quoted_identifier and ansi_nulls

The two settings quoted_identifier and ansi_nulls are special: when a procedure is created, it stores the then current values for these two settings. When the procedure is running, it re-activates those settings for the procedure.

TOO_MANY_ROWS

create table tq84_nums (
   num  integer,
   txt  varchar(10)
);

insert into tq84_nums values (1, 'one'  );
insert into tq84_nums values (2, 'two'  );
insert into tq84_nums values (2, 'TWO'  );
insert into tq84_nums values (3, 'three');

go

create  procedure tq84_too_many_rows(@num integer) as
  declare
     @txt                varchar(10)     ,
     @rowcount           integer         ,
     @warn_too_many_rows varchar(50) = '';
  select
     @txt = txt
  from
     tq84_nums
  where
     num = @num
  order by
     txt;

  set @rowcount = @@rowcount;
  if  @rowcount > 1
     set @warn_too_many_rows = ' (Warning, rowcount is ' + str(@rowcount) + ')';

  print 'txt = ' + @txt + @warn_too_many_rows;
go

exec tq84_too_many_rows 1;
--
--   Note: SQL Server, contrary to Oracle which raises TOO_MANY_ROWS,
--         does not raise an error in this case.
--         It just prints the values of the last record found.
--         This is just a disaster waiting to happen.
--
exec tq84_too_many_rows 2;
go

drop table     tq84_nums;
drop procedure tq84_too_many_rows;
go

Github repository about-MSSQL, path: /t-sql/procedures/too_many_rows.sql