if exists +procedure error

> Maybe you should first explain *WHY* you think
> you need such a stored procedure, what you would gain by having it.
> There might be a better way to achieve the same result.

I want to check a lot of tables, but I don't want to repeat code. So I
think, stored procedure will be ok for this.

This version should works properly.

create procedure spCheckTableInDatabase(
@TableName nvarchar(50)
)
as
begin
declare @sql nvarchar(60)

if exists
(select 1
from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = @TableName)
begin
select @sql = 'drop table ' + @TableName
exec(@sql)
end
end
 >> Stay informed about: if exists +procedure error 

On Dec 28, 5:05 pm, Plamen Ratchev wrote:
> You really do not have to use a lot of code to accomplish this, here is
> example:
>
> IF OBJECT_ID(N'dbo.tblDocumentInfo', N'U') IS NOT NULL
> DROP TABLE dbo.tblDocumentInfo;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Here is a slight variation that uses the INFORMATION_SCHEMA view
[ assume table is named TABLE_TO_BE_CHECKED ]:

----
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME='TABLE_TO_BE_CHECKED') DROP TABLE TABLE_TO_BE_CHECKED;
----

If you want to make the statement safer, check the table catalog and
table schema (both available in INFORMATION_SCHEMA.TABLES) and include
the catalog and schem in your "DROP" statement so you don't drop the
wrong table.

I agree with the above statement that you ought not to have to check
for the existence of tables on a regular basis. However, the
statement above is very useful for scripts.

Thanks,

Bill
 >> Stay informed about: if exists +procedure error 

On Sun, 28 Dec 2008 14:30:05 -0800 (PST), lszk wrote:

>I wrote a procedure to check, if specified table exists in db.
>
>create procedure spCheckTableInDatabase(
> @TableName nvarchar(50)
>)
>as
>begin
> select 1
> from INFORMATION_SCHEMA.TABLES
> where TABLE_SCHEMA = 'dbo'
> and TABLE_NAME = @TableName
>end
>
>I now I want to use it with if exists clause:
>
>if not exists (exec spCheckTableInDatabase 'tblDocumentInfo')
>begin
> ...
>end
>
>but it doesn't work.
>How to use a stored procedure with this clause properly?

Hi lszk,

Probably the easiest solution is not to use a stored procedure at all:

IF NOT EXISTS
(SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'tblDocumentInfo'
BEGIN
....;
END;

For a generic discussion on how to return data from a stored procedure,
see http://www.sommarskog.se/share_data.html.

However, in this specific case I have to wonder if your approach is
valid. Stored procedures are normally used for tasks that have to be
repeated over and over again. Testing for the existence of (another) a
table is not such a task. Maybe you should first explain *WHY* you think
you need such a stored procedure, what you would gain by having it.
There might be a better way to achieve the same result.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: if exists +procedure error 

You really do not have to use a lot of code to accomplish this, here is
example:

IF OBJECT_ID(N'dbo.tblDocumentInfo', N'U') IS NOT NULL
DROP TABLE dbo.tblDocumentInfo;

--
Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: if exists +procedure error 

On Sun, 28 Dec 2008 14:52:56 -0800 (PST), lszk wrote:

>I changed it to sth like that
>
>create procedure spCheckTableInDatabase(
> @TableName nvarchar(50)
>)
>as
>begin
> if exists
> (select 1
> from INFORMATION_SCHEMA.TABLES
> where TABLE_SCHEMA = 'dbo'
> and TABLE_NAME = @TableName)
> begin
> drop table @TableName --error
> end
>end
>
>and I think, it will be good. Unfortunately I have an error in line
>with comment
>Incorrect syntax near '@TableName'.
>What is wrong?

Hi lszk,

This stored procedure has the same basic problem as the previous one:
you are doing something in a stored procedure that should under most
circumstances not be a routine task.

The technical explanation in this case is that the DROP TABLE command
does not accept a variable as the table name. The only workaround is to
use dynamic SQL, which has many problems. Exposing yourself to SQL
injection attacks being the most prominent. For more details about
dynamic SQL, see http://www.sommarskog.se/dynamic_sql.html.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: if exists +procedure error 

Ok, I can't take back a post, so I have to instead give my self the
inattentiveness award, Hugo already showed a statement using the
INFORMATION_SCHEMA table, my bad.
 >> Stay informed about: if exists +procedure error 

On Sun, 28 Dec 2008 20:11:11 -0800 (PST), bill wrote:

>Ok, I can't take back a post, so I have to instead give my self the
>inattentiveness award, Hugo already showed a statement using the
>INFORMATION_SCHEMA table, my bad.

Hi Bill,

Yeah, and that example was based on the code the original poster
("lszk") had included in his post.

I normally use OBJECT_ID, such as posted by Plamen.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: if exists +procedure error