SQLSTATE - Sybase SQL Anywhere to SQL Server Migration

Sybase SQL Anywhere SQLSTATE string variable indicates the status of the last executed statement. Usually it is used to check the status of the recent FETCH statement.

In SQL Server you can use @@FETCH_STATUS variable.

Sybase SQL Anywhere SQL Server
SQLSTATE <> '02000' Row found @@FETCH_STATUS = 0
SQLSTATE = '02000' Row not found @@FETCH_STATUS <> 0

Sybase SQL Anywhere:

  -- Let's create empty sample table
  CREATE TABLE colors (name VARCHAR(30))
 
  -- Sample procedure to check "row not found" state
  CREATE PROCEDURE fetchColor1(IN @p_name VARCHAR(30))
  BEGIN
    DECLARE @v_name VARCHAR(30);
    DECLARE cur CURSOR FOR SELECT name FROM colors;
 
    OPEN cur;    
    FETCH NEXT cur INTO @v_name;
 
    -- Check if row was not found
    IF SQLSTATE = '02000' THEN
      SELECT 'No row found';
    END IF;
  END;
 
  -- Now if we call this procedure
  CALL fetchColor1('Green');
  # No row found

You can also define an exception name for SQLSTATE '02000' and use it as follows:

Sybase SQL Anywhere:

  CREATE PROCEDURE fetchColor2(IN @p_name VARCHAR(30))
  BEGIN
    DECLARE @v_name VARCHAR(30);
    DECLARE cur CURSOR FOR SELECT name FROM colors;
 
    DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000';
 
    OPEN cur;
    FETCH NEXT cur INTO @v_name;
 
    IF SQLSTATE = err_notfound THEN
      SELECT 'No row found';
    END IF;
  END;

Both examples above can be converted to SQL Server as follows:

SQL Server:

  CREATE PROCEDURE fetchColor1( @p_name VARCHAR(30))
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @v_name VARCHAR(30);
    DECLARE cur CURSOR FOR SELECT name FROM colors;
 
    OPEN cur;
    FETCH NEXT FROM cur INTO @v_name;
 
    -- Check if row was not found
    IF @@FETCH_STATUS <> 0 BEGIN
      SELECT 'No row found';
    END 
  END;
  GO
 
  -- Now if we call this procedure
  EXEC fetchColor1 'Green';
  # No row found

Now let's consider “Row found” example:

Sybase SQL Anywhere:

  -- Let's insert a sample row
  INSERT INTO colors VALUES ('Green');
 
  -- Sample procedure to check "row found" state
  CREATE PROCEDURE fetchColor3(IN @p_name VARCHAR(30))
  BEGIN
    DECLARE @v_name VARCHAR(30);
    DECLARE cur CURSOR FOR SELECT name FROM colors;
 
    OPEN cur;
    FETCH NEXT cur INTO @v_name;
 
    -- Check if row was found
    IF SQLSTATE <> '02000' THEN
      SELECT 'Row found';
    END IF;
  END;
 
  -- Now if we call this procedure
  CALL fetchColor3('Green');
  # Row found

And its conversion to SQL Server:

SQL Server:

  -- Let's insert a sample row
  INSERT INTO colors VALUES ('Green');
 
  CREATE PROCEDURE fetchColor3( @p_name VARCHAR(30))
  AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @v_name VARCHAR(30);
    DECLARE cur CURSOR FOR SELECT name FROM colors;
 
    OPEN cur;
    FETCH NEXT FROM cur INTO @v_name;
 
    -- Check if row was found
    IF @@FETCH_STATUS = 0 BEGIN
      SELECT 'Row found';
    END 
  END;
  GO
 
  -- Now if we call this procedure
  EXEC fetchColor3 'Green';
  # Row found

For more information, see Sybase SQL Anywhere to SQL Server Migration.