@@SQLSTATUS - Sybase SQL Anywhere to SQL Server Migration

Sybase SQL Anywhere @@SQLSTATUS variable indicates the status of the last executed statement. It can be used to check the status of the recent FETCH statement.

In SQL Server you can use @@FETCH_STATUS variable.

Sybase SQL Anywhere SQL Server
@@SQLSTATUS = 0 Row found @@FETCH_STATUS = 0
@@SQLSTATUS = 2 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 @@SQLSTATUS = 2 THEN
      SELECT 'No row found';
    END IF;
  END;
 
  -- Now if we call this procedure
  CALL fetchColor1('Green');
  # No row found

This example 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 fetchColor2(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 @@SQLSTATUS = 0 THEN
      SELECT 'Row found';
    END IF;
  END;
 
  -- Now if we call this procedure
  CALL fetchColor2('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.