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.