[Oracle to SQL Server Migration] How to convert Oracle User-Defined Function to SQL Server

Here is my oracle user-defined function:

1 2 3 4 5 6 7 8 9 10 11 12 13

create or replace FUNCTION F_DBM_FIND_CODE_NM (PI_KIND_CODE IN VARCHAR2, PI_CODE_NO IN NUMBER) RETURN VARCHAR2 IS CODE_NAME NVARCHAR2(50); V_KIND_CODE VARCHAR2(5) := PI_KIND_CODE; V_CODE_NO NUMBER (10) := PI_CODE_NO; V_SQLSTR VARCHAR2 (200) := ''; BEGIN V_SQLSTR := 'SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = :1 AND CODE_NO = :2'; EXECUTE IMMEDIATE V_SQLSTR INTO CODE_NAME USING V_KIND_CODE, V_CODE_NO; RETURN CODE_NAME; END F_DBM_FIND_CODE_NM;

But the syntax has big difference in Microsoft SQL Server:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--Transact-SQL Scalar Function Syntax CREATE FUNCTION [ schema_name. ] function_name( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] [ READONLY ] } [ ,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ]

Hence, the oracle user-defined function will be translated as bellows:

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE FUNCTION AP_NTA.F_DBM_FIND_CODE_NM ( @PI_KIND_CODE VARCHAR(5), @PI_CODE_NO NUMERIC(10,0) ) RETURNS NVARCHAR(50) BEGIN RETURN ( SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = @PI_KIND_CODE AND CODE_NO = @PI_CODE_NO ) END ;

Test function in Microsoft SQL Server:

select AP_NTA.F_DBM_FIND_CODE_NM('IK', 47) as codeName;

Reference
[1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
[2] https://msdn.microsoft.com/en-us/library/ms186755.aspx