Results 1 to 11 of 11

Thread: Transact Sql

  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: Transact Sql

    Is it possible to capture and display "user - defined" error message for the following case.

    Select rows from a table which does not exist in the database..

    DatabaseYBASE
    Last edited by s_somu; 10-07-03 at 19:15.

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    This should work


    if not exists ( select * from sysobjects where name='TABLENAME' and type='U')
    print "MYERROR"

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    Thanks for the reply ...but that solution may not work in this case

    Script1 creates table in tempdb.Script2 try to access the table from another database on the same server.

    While executing the script2 - we want capture and display the error message if the table does not exists( saying "Pls execute script1"...)

    Any ideas to implement the above

  4. #4
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Re: Transact Sql

    Hi Somu,

    I think it should still work.. I am assuming you are creating a permenant table in tempdb.

    In Script2 include the following code:

    if not exists( select * from tempdb..sysobjects where name='TABLE' and type='U')
    print "RUN SCRIPT1"

  5. #5
    Join Date
    Oct 2003
    Posts
    6
    Hi trvishi,

    Snapshot of the script:
    --------------------------------------------------------
    If object_id ('tempdb.guest.tablename') is not null

    begin

    select * from tempdb.guest.tablename

    .....

    end

    else

    Print 'Run script 1'
    ------------------------------------------------------------------

    Problem:

    While executing the above script without running script1 - sybase throws a message saying table doesn't exist...

    Is it possible to handle that error?...If Sybase throws the error, we want give a message saying "table doesn't exists" instead of abruptly coming out the script.

    Thanks

  6. #6
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Did u try the "if exists (select * from sysobjects)" instead of the object_id() ?

  7. #7
    Join Date
    Oct 2003
    Posts
    6
    Yes i tried it...Got error message Table not found

  8. #8
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    I am assuming you are saying that the "table not found" is for the user table which you are creating. Then it means for some reasons your "if exists" condition is not working as desired.

    This should definitely work. Also make sure to check the permissions.
    Try running the "select * from tempdb..sysobjects where name='TABLENAME' and type='U'" alone and see what happens.

    if exists(select * from tempdb..sysobjects where name='TABLENAME' and type='U')
    /* DO PROCESSSING */
    else
    /* RAISE ERROR */

  9. #9
    Join Date
    Oct 2003
    Posts
    6
    if exists(select * from tempdb..sysobjects where name='TABLENAME' and type='U')
    DROP table TABLENAME
    else
    /* RAISE ERROR */


    The above works.....but the following is not working


    if exists(select * from tempdb..sysobjects where name='TABLENAME' and type='U')
    select * from TABLENAME
    else
    /* RAISE ERROR */

    Note: TABLENAME doesn't exists in the database
    Last edited by s_somu; 10-09-03 at 10:33.

  10. #10
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Hi Somu,

    You missed the "tempdb.." in the select statement.

    if exists(select * from tempdb..sysobjects where name='TABLENAME' and type='U')
    select * from tempdb..TABLENAME /* tempdb.. was missing here */
    else
    /* RAISE ERROR */

  11. #11
    Join Date
    Oct 2003
    Posts
    6
    Hi,

    While posting forgot to add tempdb prefix.


    if exists(select * from tempdb..sysobjects where name='TABLENAME' and type='U')
    DROP table tempdb.guest.TABLENAME
    else
    /* RAISE ERROR */


    The above works.....but the following is not working


    if exists(select * from tempdb..sysobjects where name='TABLENAME' and type='U')
    select * from tempdb.guest.TABLENAME
    else
    /* RAISE ERROR */

    Note: TABLENAME doesn't exists in the database

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •