Convert PL/sql to T-SQL

Question

I have following pl/sql and I want to convert it to T-SQL. I googled but did not find any article.

DECLARE
    l_retval    VARCHAR2(32767);
    l_rows      VARCHAR2(32767);
BEGIN
    FOR i IN (SELECT  emp.empno AS empno
                     ,emp.ename AS ename
                     ,mgr.empno AS mgr_empno
                     ,mgr.ename AS mgr_ename
              FROM    emp emp
                     ,emp mgr
              WHERE   emp.mgr = mgr.empno(+)
             )
    LOOP
        l_rows := l_rows||'{''emp_empno'':'''||i.empno||''',''emp_ename'':'''||i.ename||''',''mgr_empno'':'''||i.mgr_empno||''',''mgr_ename'':'''||i.mgr_ename||'''},';
    END LOOP;

    l_retval := '['||rtrim(l_rows,',')||']';

    htp.prn(l_retval);
END;

Do you mean something like this http://stackoverflow.com/questions/6818441/sql-server-select-to-json-function ? — A.B.Cade, Oct 29 '13 at 07:25
One thing to watch out for - if you're building JSON, technically, you cannot have single-quotes around your property names and string values - you must use double-quotes. Some frameworks are forgiving about this, and some aren't. — Joe Enos, Oct 29 '13 at 07:30
@ A.B.Cade:Please post the link in answer so that I can accept it as answer — , Oct 29 '13 at 07:34
@user2893534, no need, as it seems to be a duplicated answer, better close the question and upvote the helpfull answers in the link — A.B.Cade, Oct 29 '13 at 07:43
how is PL SQL to TSQL on topic, but TSQL to MySQL is off topic? Consistency is good... — Volvox, Oct 29 '13 at 18:30
Microsoft has a migration assistant at http://technet.microsoft.com/en-us/library/hh313179.aspx — Russell Fox, Nov 06 '13 at 00:41

score 0 · Answer 1 · answered Dec 11 '14 at 13:16

You can use cursor and get the following T-SQL code:

DECLARE @l_retval varchar(max) 
   DECLARE @l_rows varchar(max) 
   DECLARE @SWV_I_empno VARCHAR(255) -- data type of column emp.empno 
   DECLARE @SWV_I_ename VARCHAR(255) -- data type of column emp.ename 
   DECLARE @SWV_I_mgr_empno VARCHAR(255) -- data type of column mgr.empno 
   DECLARE @SWV_I_mgr_ename VARCHAR(255) -- data type of column mgr.enam 
   DECLARE @SWV_cursor_var1 CURSOR 
   SET @SWV_cursor_var1 = CURSOR FOR SELECT emp.empno AS empno 
                     ,emp.ename AS ename 
                     ,mgr.empno AS mgr_empno 
                     ,mgr.ename AS mgr_ename 
   FROM emp emp LEFT OUTER JOIN emp mgr ON emp.mgr = mgr.empno 
   OPEN @SWV_cursor_var1 
   FETCH NEXT FROM @SWV_cursor_var1 INTO @SWV_I_empno,@SWV_I_ename,@SWV_I_mgr_empno,@SWV_I_mgr_ename 
   while @@FETCH_STATUS = 0 
   begin 
      SET @l_rows = @l_rows+'{''emp_empno'':'''+@SWV_I_empno+''',''emp_ename'':'''+@SWV_I_ename+''',''mgr_empno'':'''+@SWV_I_mgr_empno+''',''mgr_ename'':'''+@SWV_I_mgr_ename+'''},' 
      FETCH NEXT FROM @SWV_cursor_var1 INTO @SWV_I_empno,@SWV_I_ename,@SWV_I_mgr_empno,@SWV_I_mgr_ename 
   end 
   CLOSE @SWV_cursor_var1 

   SET @l_retval = '['+rtrim(@l_rows)+']' 

   EXECUTE HTP.prn @l_retval

Convert PL/sql to T-SQL

1 Answers1