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.