Way for moving stored procedure from Firebird to SQL Server -

i have working sp on firebird , can'not find way translate code ms sql server sp

here code:

i have 1 table data from. 1 fields called "iznos" data summary , 1 field recognite diference summary category

create or alter procedure some_procedure (         b_date date,         e_date date)    returns(         dat date,         value1 decimal(18,2),         value2 decimal(18,2),         value3 (18,2))      begin              select gk.date       gk                gk.date between :b_date , :e_date       group 1       :dat             begin           /* value 1 */           select sum(iznos) gk gk.category=1 , gk.datum=:dat           :value1;           /* value 2 */           select sum(iznos) gk gk.category=2 , gk.datum=:dat           :value2;           /* value 3 */           select sum(iznos) gk gk.category=3 , gk.datum=:dat           :value3;           suspend;       end   end   

you can solve using pivot:

select datum, [1] value1, [2] value2, [3] value3 (     select datum, category, iznos      gk      datum between @b_date , @e_date  ) src pivot (     sum(src.iznos)     src.category in ([1], [2], [3]) ) pvt 

this doesn't have exact same effect firebird stored procedure though, because returning row each row in gk. if want have exact same result, may have join gk above query.

if intend use stored procedure in select (which possible in firebird, not in sql server), need convert table-valued user-defined function.

you create view instead. in case where clause need removed.

small example in tsql:

create table #example (    datum date,    category int,    val int );  insert #example(datum, category, val) values     ('2015-07-24', 1, 1),     ('2015-07-24', 2, 1),     ('2015-07-24', 3, 1),     ('2015-07-24', 1, 1),     ('2015-07-24', 2, 1),     ('2015-07-25', 3, 1);  select datum, [1] value1, [2] value2, [3] value3 (     select datum, category, val      #example  ) src pivot (     sum(src.val)     src.category in ([1], [2], [3]) ) pvt;  drop table #example;