Check for table and return true or false

GA_KEN · #1 (**permalink**) 02-28-03, 11:27

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

GA_KEN · #2 (**permalink**) 02-28-03, 11:32

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!

Paul Young · #3 (**permalink**) 02-28-03, 12:29

you could also use:

declare @TableName sysname
set @TableNAme = 'sysobjects'
if OBJECTPROPERTY(OBJECT_ID(@TableName),'IsTable') = 1
print "+@ISYES+"
else
print "+@ISNO+"

GA_KEN · #4 (**permalink**) 02-28-03, 12:30

I have another problem now...

How do I get the return value?

Paul Young · #5 (**permalink**) 02-28-03, 12:32

do you want it as a result set, output parameter or as a numeric valued returned by the "RETURN" statement?

GA_KEN · #6 (**permalink**) 02-28-03, 12:34

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

Paul Young · #7 (**permalink**) 02-28-03, 12:39

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'

GA_KEN · #8 (**permalink**) 02-28-03, 12:42

Too Cool! Thank you so much!

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

Thanks for your help!

Ken

Paul Young · #9 (**permalink**) 02-28-03, 12:43

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'

Internet Services

Web Development

Digital Marketing

Consumer Tech