none
Conversion of Oracle function into SQL Server 2008

    Question

  • Please help to convert this Oracle function into SQL Server function.

    I am very new to SQL Server functions.

    Thank you.

    Oracle Function:

     


    • Edited by knra Thursday, November 28, 2013 6:47 AM
    Sunday, November 10, 2013 4:24 PM

Answers

  • Try SSMA......

    here is the link to download - > http://www.microsoft.com/en-us/download/details.aspx?id=28763


    Regards Harsh

    Sunday, November 10, 2013 4:36 PM
  • I am certainly not going to write the corresponding T-SQL code for you. I don't know PL/SQL, and I don't like reading code which is all in uppercase.

    I will however, make a few remarks:

    *  An SQL Server function must not change database state, why you cannot have an INSERT into a permanent table as you seem to have in this function. Presumably, you want a stored procedure instead.

    *  I see that there are a couple of cursors. I know that cursors are fairly commonly used in Oracle. And you can certainly find a lot of examples in SQL Server code as well. However, beware that cursors do not perform well in SQL Server. Or more exactly: using loops over a set-based statement often comes with a high penalty in performance.

    * The || operator in Oracle (and ANSI SQL) does not exist in SQL Server. Use + instead.

    * I see that you use "FROM dual" in the function. In SQL Server you achieve the same thing by simply omitting the FROM clause.

    Particularly with the second point in mind, I recommend that you make a fresh start and analyse what your function does. It is not unlikely that you can replace it with a single statement.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 10, 2013 5:55 PM
  • In addition to Erland's reply:

    * You can use the standard COALESCE() function to return the first non-NULL value within the provided list. You probably need it to replace NVL()

    * I am not sure SQL Server support the SUBSTR abbreviation of the SUBSTRING function

    * You can use the standard CASE expression to replace the DECODE function

    * I guess you can use CEILING to replace the CEIL function

    * In one of the cursors, you are using the non-deterministic outer join syntax "(+)".  I strongly advise you to rewrite all joins in that query to INNER JOIN / OUTER JOIN instead of listing all tables and then joining them in the WHERE clause.

    * I guess you can use CHARINDEX to replace the INSTR function

    * "!=" will work in SQL Server, but "<>" is the standard notation for "not equal to"

    * In TSQL, there is no ELSIF. There is only IF and ELSE. So you would need to nest the IFs to achieve the same, or change to a CASE expression where applicable.

    And I agree with Erland that you can probably rewrite this entire (hard to read) piece of code to two INSERT statements.


    Gert-Jan

    Sunday, November 10, 2013 11:21 PM

All replies

  • Try SSMA......

    here is the link to download - > http://www.microsoft.com/en-us/download/details.aspx?id=28763


    Regards Harsh

    Sunday, November 10, 2013 4:36 PM
  • In addition, please visit this blog:

    SQL Server Migration Assistant (SSMA) Team's Blog


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Sunday, November 10, 2013 5:30 PM
  • I am certainly not going to write the corresponding T-SQL code for you. I don't know PL/SQL, and I don't like reading code which is all in uppercase.

    I will however, make a few remarks:

    *  An SQL Server function must not change database state, why you cannot have an INSERT into a permanent table as you seem to have in this function. Presumably, you want a stored procedure instead.

    *  I see that there are a couple of cursors. I know that cursors are fairly commonly used in Oracle. And you can certainly find a lot of examples in SQL Server code as well. However, beware that cursors do not perform well in SQL Server. Or more exactly: using loops over a set-based statement often comes with a high penalty in performance.

    * The || operator in Oracle (and ANSI SQL) does not exist in SQL Server. Use + instead.

    * I see that you use "FROM dual" in the function. In SQL Server you achieve the same thing by simply omitting the FROM clause.

    Particularly with the second point in mind, I recommend that you make a fresh start and analyse what your function does. It is not unlikely that you can replace it with a single statement.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 10, 2013 5:55 PM
  • In addition to Erland's reply:

    * You can use the standard COALESCE() function to return the first non-NULL value within the provided list. You probably need it to replace NVL()

    * I am not sure SQL Server support the SUBSTR abbreviation of the SUBSTRING function

    * You can use the standard CASE expression to replace the DECODE function

    * I guess you can use CEILING to replace the CEIL function

    * In one of the cursors, you are using the non-deterministic outer join syntax "(+)".  I strongly advise you to rewrite all joins in that query to INNER JOIN / OUTER JOIN instead of listing all tables and then joining them in the WHERE clause.

    * I guess you can use CHARINDEX to replace the INSTR function

    * "!=" will work in SQL Server, but "<>" is the standard notation for "not equal to"

    * In TSQL, there is no ELSIF. There is only IF and ELSE. So you would need to nest the IFs to achieve the same, or change to a CASE expression where applicable.

    And I agree with Erland that you can probably rewrite this entire (hard to read) piece of code to two INSERT statements.


    Gert-Jan

    Sunday, November 10, 2013 11:21 PM