MDBitz Development Resources

When writing SQL we may find that we need to write a WHERE clause that checks if a columns is not a certain value. To do this we have the <> operator that means does not equal. However you may be surprised that you are not getting all the results you expected, this is because <> does not match null values. To get all values that are not equal you need to check that a value is not equal or that it is null.

Examples SQL

SELECT * FROM CLIENT
WHERE SENSITIVE <> 'Y' or SENSITIVE IS null

You may find that when developing Select queries for an Application that you may want to dynamically limit the results returned from a Stored Procedure. Upon first inspection you may think oh lets use a TOP clause with a limit variable passed in when the stored procedure is called.

CREATE PROCEDURE getROWS
    @LIMIT INT
AS
BEGIN
    SELECT TOP @LIMIT *
    FROM TABLE
    ORDER BY modTS DESC
END
GO

Unfortunately Sybase T-SQL does not allow variables in the limit clause. Don’t worry though the ROWCOUNT variable can be paramaterized. This gives us the following procedure that limits the results returned to our desired amount.

CREATE PROCEDURE getROWS
    @LIMIT INT
AS
BEGIN
    SET ROWCOUNT @LIMIT
    SELECT *
    FROM TABLE
    ORDER BY modTS DESC
    SET ROWCOUNT 0
END
GO

That is all there is to it, simply remember to use a ROWCOUNT statement instead of a TOP clause in your stored procedures.

Most individuals having database experience with MySQL will find shortly after using Sybase DBMS that the backslash character does not escape characters. Instead in Sybase T-SQL the single quote itself acts as the escape character. Below is a sample UPDATE statement showing the difference in escaping the single quote character in MySQL and Sybase.

MySQL

UPDATE COMPANY SET NAME = 'Joseph\'s Shoes' WHERE ID = 3

Sybase

UPDATE COMPANY SET NAME = 'Joseph''s Shoes' WHERE ID = 3

A Cursor is a database structure that allows for the traversal of the records in a result set. In Sybase T-SQL a cursor functions by the following steps:

1. Declare the Cursor:
   To use a cursor you first must declare it by providing a name and the SELECT query that will provide the result set it will be traversing through.  The Syntax for declaring the Cursor is as follows:

   DECLARE cursor_name CURSOR
   FOR  SELECT ....

2. Open the Cursor:
   Once the Cursor is declared we must open the cursor to utilize it:

   OPEN cursor_name

3. Fetch Rows:
   Now that the Cursor is ready we use the FETCH command to return a result row.  When fetching a row we need to fetch it into variables defined prior to the Fetch. To provide the user with if the query was successful the @@sqlstatus variable is available, where 0 means success, 1 = error, and 2 is no more data. We can use the @@sqlstatus variable in a WHILE condition to traverse the entire result set.

   FETCH cursor_name INTO @var1, @var2, ...
   WHILE ( @@sqlstatus = 0 )
   BEGIN
     // do something WITH the DATA
     FETCH cursor_name INTO @var1, @var2, ...
   END

4. Close the Cursor:
   After traversal of the result set we close the cursor by use of the CLOSE command:

   CLOSE cursor_name

5. Deallocate the Cursor:
   Last we deallocate the cursor so that Cursor is removed from memory and can not be opened again:

   DEALLOCATE CURSOR cursor_name

Optimization

Be advised that when using SELECT statements that JOIN multiple tables or have sub-queries that performance can be affected. To optimize it is good practice to perform such selects into a temporary table and that the cursor is set up to select data from that temp table.

Resources

• Sybase T-SQL Manual – Cursors

SQL Queries can often be written in multiple ways to obtain the same end result set. Tables for instance can be joined in the FROM clause or the WHERE clause of a SQL Statement. In the FROM Clause we can utilize LEFT, RIGHT, INNER keywords to denote if both sides of the statement needs to be present to return the results or if only 1 side needs to be.  These same joins can be accomplished in the WHERE clause by use of the *= and =*.  Where *= is equivalent to LEFT JOIN and =* is the same as RIGHT JOIN.

Example FROM clause LEFT JOIN

FROM USER u 
  LEFT JOIN ROLE r ON u.ROLE_ID = r.ID

Example WHERE clause LEFT JOIN

FROM USER u,
  ROLE r
WHERE u.ROLE_ID *= r.ID