Argument-Passing Help

[christywarner]

Hi,

I want to create a procedure "EXISTDROP" that
will drop a SQL Server table where the table name
is passed from my VB app. Here is what I have
but I'm getting the error message:

"Incorrect syntax near @TABLENAME on line 5"

CREATE PROCEDURE EXISTDROP
@TABLENAME VARCHAR(75) AS
IF EXISTS (SELECT name FROM sysobjects WHERE NAME= @TABLENAME)
DROP @TABLENAME
GO

Thanks for any help!
Christy.

 

[christywarner]

never-mind, figured it out, it needed
some brackets around the table-name in
the DROP statement. . .

Thanks,
Christy.

 

[ESquared]

Have you tested it to make sure it actually works?

Putting brackets around the table name makes the syntax checker happy, but doesn't make it drop the table whose name is stored in the variable. Instead, it will try to drop the table '@tablename' which is not one of your tables, I am sure.

You'll need to use dynamic SQL to accomplish this task.

As for the advisability of such a stored procedure, why can't you just use templates? It's good to avoid dynamic SQL when possible.