Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Unanswered: Check for table and return true or false

    How can I write a stored procedure to return if a table exist or not?

    I put:

    Code:
    CREATE procedure sp_BA_ReportExist
    
    (
    @ISYES VARCHAR (10),
    @ISNO VARCHAR (10)
    )
    
    AS
    
    DECLARE @SQL varchar(8000)
    SET @SQL = " if object_id('BA_REPORT_MASTER') is not null  RETURN  "+@ISYES+"  ELSE RETURN "+@ISNO+" "
    
    EXEC(@SQL)
    GO
    I ran it with: sp_BA_ReportExist '1','0'

    but I get:

    Code:
    Server: Msg 178, Level 15, State 1, Line 1
    A RETURN statement with a return value cannot be used in this context.
    Server: Msg 178, Level 15, State 1, Line 1
    A RETURN statement with a return value cannot be used in this context.
    How can I make this work?

    Thanks!

    Ken

  2. #2
    Join Date
    Jan 2003
    Posts
    126
    Figured it out... this works:


    Code:
    
    CREATE procedure sp_BA_ReportExist
    
    (
    @ISYES VARCHAR (10),
    @ISNO VARCHAR (10)
    )
    
    AS
    
    DECLARE @SQL varchar(8000)
    SET @SQL = " if object_id('BA_REPORT_MASTER') is not null  PRINT  "+@ISYES+"  ELSE PRINT "+@ISNO+" "
    
    EXEC(@SQL)
    GO
    As always I find the answer right after I post!

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    you could also use:

    declare @TableName sysname
    set @TableNAme = 'sysobjects'
    if OBJECTPROPERTY(OBJECT_ID(@TableName),'IsTable') = 1
    print "+@ISYES+"
    else
    print "+@ISNO+"
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jan 2003
    Posts
    126
    I have another problem now...

    How do I get the return value?

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jan 2003
    Posts
    126
    A resultset will work.

    Basically I just need to know if the table exists so my application can set some values. Ic na't figure out how to get the value back into the application.

    Thanks so much for any light you can shed on this!

    Ken

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    try:

    Code:
    create procedure sp_BA_ReportExist(
      @ISYES VARCHAR (10)
    , @ISNO  VARCHAR (10))
    AS
    if (object_id('BA_REPORT_MASTER') is not null)
      select @ISYES as Answer
    else
      select @ISNO as Answer
    
    return 0
    GO
    
    exec sp_BA_ReportExist 'Yes', 'No'
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Jan 2003
    Posts
    126
    Too Cool! Thank you so much!

    I was kinda close, but didn't have it quite right!

    Thanks for your help!

    Ken

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    or:
    Code:
    create procedure sp_TableExists(
      @TableName sysname
    , @ISYES     VARCHAR (10) = 'Yes'
    , @ISNO      VARCHAR (10) = 'No')
    AS
    select case OBJECTPROPERTY(OBJECT_ID(@TableName),'IsTable') when 1 then @ISYES else @ISNO end as Answer
    return 0
    GO
    
    exec sp_TableExists 'sysobjects','Yes', 'No'
    
    or just 
    
    exec sp_TableExists 'sysobjects'
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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