Code conversion patterns to migrate Sybase SQL Anywhere database schemas to Amazon RDS for SQL Server or Microsoft SQL Server on Amazon EC2

SELECT 
       LEN('AWSBLOGPOST')         as str1,
       LEN('  AWSBLOGPOST')       as str2,
       LEN('AWSBLOGPOST  ')       as str3,
       LEN('  AWSBLOGPOST  ')     as str4

BEGIN 
    DECLARE @document VARCHAR(64);  

    SELECT @document = 'Reflectors are vital safety' +
                       ' components of your bicycle.'; 
 
    SELECT LOCATE(@document, 'vital'); 
 
    SELECT LOCATE(@document, 'vital',17);  
END

BEGIN
    DECLARE @document VARCHAR(64);  

    SELECT @document = 'Reflectors are vital safety' +  
                       ' components of your bicycle.';  
    
    SELECT CHARINDEX('vital', @document);  

    SELECT CHARINDEX('vital', @document, 17);  
END

SELECT SUBSTRING('AWSBLOGPOST',4)

SELECT SUBSTRING('AWSBLOGPOST',4, 
                 LEN('AWSBLOGPOST'))

CREATE TABLE DEMO(
                   id INTEGER, 
                   ename CHAR(10), 
                   doj DATE, 
                   address VARCHAR(255)
                  )

INSERT INTO DEMO VALUES 
(100, 'Jitendra', '2004-01-01', '99, XYZ Street, India – 999999')
INSERT INTO DEMO VALUES 
(1000, 'Bhavani', '2005-12-31', '101, ABC Street, India - 123456 ')

SELECT 
    DATALENGTH(id) as dl_id, 
    DATALENGTH(ename) as dl_ename,
    DATALENGTH(doj) as dl_doj, 
    DATALENGTH(address) as dl_address
FROM DEMO

CREATE TABLE DEMO(
                   id INTEGER, 
                   ename CHAR(10), 
                   doj DATE, 
                   address VARCHAR(255)
                  )

INSERT INTO DEMO VALUES 
(100, 'Jitendra', '2004-01-01', '99, XYZ Street, India – 999999')
INSERT INTO DEMO VALUES 
(1000, 'Bhavani', '2005-12-31', '101, ABC Street, India - 123456 ')

SELECT 
    DATALENGTH(id) as dl_id,   
    DATALENGTH(ename) as dl_ename,
    DATALENGTH(doj) as dl_doj, 
    DATALENGTH(address) as dl_address
FROM DEMO

BEGIN 
    DECLARE @gid INTEGER
    DECLARE @name VARCHAR(100)

    SELECT 
        gid, name INTO @gid, @name 
    FROM sysusers WHERE uid = 0;

    SELECT @gid, @name
END

BEGIN 
    DECLARE @gid INTEGER
    DECLARE @name VARCHAR(100)

    SELECT 
        @gid = gid, @name = name 
    FROM sysusers 
    WHERE uid = 4;

    SELECT @gid, @name
END

SELECT id, ename INTO DEMO_NEW FROM DEMO

SELECT * FROM DEMO_NEW

SELECT id, ename INTO DEMO_NEW FROM DEMO

SELECT * FROM DEMO_NEW

PRINT ' Timestamp = %1!', GETDATE()

PRINT CONCAT(' Timestamp = ' , GETDATE())

begin
    declare @lAmountValue decimal(12,2)
    declare @lComponent   char(1) = 'T'
    declare @lPcTicket    decimal(12,2) = 111.99
    declare @lPcTax       decimal(12,2) = 222.99
    declare @lPcOther1    decimal(12,2) = 333.99

    BEGIN
        CASE @lComponent
          WHEN 'T'    THEN  SELECT @lAmountValue = @lPcTicket
          WHEN 'X'    THEN  SELECT @lAmountValue = @lPcTax
          WHEN '1'    THEN  SELECT @lAmountValue = @lPcOther1
        END 
    END

SELECT   
     @lComponent,@lPcTicket,@lPcTax,
     @lPcOther1,@lAmountValue
end

begin
    declare @lAmountValue decimal(12,2)
    declare @lComponent char(1) = 'T'
    declare @lPcTicket decimal(12,2) = 111.99
    declare @lPcTax decimal(12,2)    = 222.99
    declare @lPcOther1 decimal(12,2) = 333.99

    BEGIN
        SELECT @lAmountValue = 
            CASE @lComponent
                WHEN 'T'       THEN            @lPcTicket
                WHEN 'X'       THEN            @lPcTax
                WHEN '1'       THEN            @lPcOther1
            END
    END

    SELECT @lComponent,@lPcTicket,
                 @lPcTax,@lPcOther1,@lAmountValue
end

CREATE TABLE DEMO(
                   id INTEGER, 
                   ename CHAR(10), 
                   doj DATE, 
                   address VARCHAR(255)
                  );

CREATE GLOBAL TEMPORARY TABLE DEMO_SHARED_TEMP
                 (
                   EmpID, 
                   EmpName,DOJ
                 )
AS (SELECT id, ename, doj FROM DEMO)
ON COMMIT PRESERVE ROWS
SHARE BY ALL 
WITH NO DATA;

SELECT * FROM DEMO_SHARED_TEMP;

CREATE TABLE DEMO(
                   id INTEGER, 
                   ename CHAR(10), 
                   doj DATE, 
                   address VARCHAR(255)
                  );
SELECT 
      id             as EmpID , 
      ename          as EmpName , 
      doj            INTO DEMO_SHARED_TEMP
FROM DEMO;

SELECT * FROM DEMO_SHARED_TEMP;

begin
    DECLARE @oSqlCode        integer      
    DECLARE @oSqlState       char( 5)
    DECLARE @oErrorMsg       varchar(254) 
    DECLARE @empName         varchar(254)

    SELECT
       @empName = ename
    FROM demo
    WHERE id = 1000
    
    SELECT @oSqlCode  = SQLCODE
         , @oSqlState = SQLSTATE

    IF @oSqlState = '02000'  -- record not found 
      BEGIN
        SELECT @returnCode       = -1
        SELECT @oErrorMsg = 'Employee ID not found.'
      END
    ELSE
    IF @oSqlState <> '00000' -- error in SELECT query
      BEGIN
        SELECT @oErrorMsg   = 'ERROR executing SELECT'
      END

    select @empName as EmployeeName, 
           string('@oSqlState=',@oSqlState,', 
                   @oSqlCode=',@oSqlCode,',
                   @oErrorMsg=',@oErrorMsg) as Output
end

begin
    DECLARE @oSqlCode     integer
    DECLARE @oSqlState    char( 5)
    DECLARE @oErrorMsg    varchar(254)
    DECLARE @RowCount     int
    DECLARE @empName      varchar(254)

    SELECT
       @empName = ename
    FROM demo
    WHERE id = 1000

    SELECT @oSqlCode    = @@ERROR
         , @RowCount    = @@ROWCOUNT 

    IF @oSqlCode = 0  
    BEGIN
        IF @RowCount = 0   -- record not found
        BEGIN
            SELECT @oErrorMsg = 'Employee ID not found.'
        END
    END        
    ELSE                   -- Error in SELECT query   
    BEGIN
      SELECT @oErrorMsg   = 'ERROR executing SELECT'
    END

    select @empName as EmployeeName, 
      concat('@oSqlCode=',@oSqlCode,',
              @RowCount=',@RowCount,',
              @oErrorMsg=',@oErrorMsg) as OUTPUT
end