SQL Server T-SQL alternative

Question

I'm having some trouble writing functions in T-SQL for SQL Server 2014.

I understand that T-SQL functions are not allowed to execute things like UPDATE/DELETE etc but this is a problem for me because I have several SQL processes that I use for PostgreSQL that do exactly what I want but I can't make a SQL Server version of them because of this limitation.

For example I have the following (really watered down version) of a process I use on PostgreSQL.

drop type if exists localtype cascade;
create type localtype as (
   id integer,
   status text
);

drop function if exists rowmaintenance();
create function rowmaintenance(total integer, status text)
returns setof localtype as $$

   declare
      result localtype;
      maincursor record;

begin

   for maincursor in
      select * from colours
      where co_num <= total
   loop

      update colours
      set co_status = status;

      result.id := maincursor.co_num;
      result.status = status;

      return next result;

   end loop;

end $$
language 'plpgsql';

select * from rowmaintenance(10, 'active');

Many of our processes will run through records and make updates and then return results (several rows) of what has been updated. I am unable to achieve this in T-SQL. Is there an alternate language or way to achieve something similar in T-SQL?

I've tried T-SQL functions but the limitations are too severe

*Stored Procedures* have no such limitations & seem more appropriate to your use case, see [Function vs. Stored Procedure in SQL Server](http://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server) — Alex K., Sep 12 '16 at 15:05
this is something a stored procedure can do with no problems — Lamak, Sep 12 '16 at 15:05
can stored procedures return multiple rows of results though? — TheLovelySausage, Sep 12 '16 at 15:05
Yes they can, you can SELECT whatever you like. (The only limitation is that you cannot use the invocation as a row/scalar value source inside *another* single statement as you can with a function) — Alex K., Sep 12 '16 at 15:08
@AlexK. do you have any recommendations of where I can look to see some examples — TheLovelySausage, Sep 12 '16 at 15:42
But really you should get rid of the looping. This looks like nothing more than a single update statement. — Sean Lange, Sep 12 '16 at 15:49
this one is very simple for explanation purposes the actual thing is a lot more involved — TheLovelySausage, Sep 12 '16 at 15:51

SQL Server T-SQL alternative

0 Answers0