Delete Query 2

[rookery]

Why isnt the following stored proc not executing:

CREATE PROCEDURE spDeleteTable

@txtTableName nvarchar(50)

AS

DELETE txtTableName
GO

I keep getting this error:

Invalid object name 'txtTableName'.

I know it's probably something stupid but can anyone help?

 

[WhiteKnight57t]

Well you are trying to delete table called txtTableName

What you want to delete is the Actual table name that you passed into the SP

And By the way this is a very BAD idea for a stored procedure I'd be very worried that you delete tables this way!

==========================
Sorry to be terse
some say it's a curse
I know it's worse
I'm just diverse
-Tim

 

[rookery]

Thanks for your reply.

When I call the SP from the query window I am passing the table name to the query.

Is there something wrong with the execution command:

EXEC spDeleteTable [Tbl Example1]

 

[SQLBill]

As WhiteKnight57t says, you are deleting txtTableName and not the inputted variable @txtTableName. Those are two very different things.

It should be DELETE @txtTableName.

And I agree with WhiteKnight57t...this is a bad idea. There's no checking being done, I could mistype a name and delete the wrong table...

EXEC spDeleteTable 'Master'
GO


-SQLBill

 

[rookery]

SQLBill when I try what you suggest and hit the Check Syntax button I get a message saying:

"You must declare the variable @txtTableName"

I thought I'd already done that?

 

[dbomrrsm]

bad idea but:

CREATE PROCEDURE spDeleteTable
@txtTableName nvarchar(50)
AS
declare @sql nvarchar (100)
set @sql = 'DELETE '+@txtTableName
exec (@sql)

[blue]DBomrrsm[/blue]

 

[rookery]

Wrongs and rights aside why cant I just execute the proc from the query window and pass in the table name?

As I say above if I type:

CREATE PROCEDURE spDeleteTable

@txtTableName nvarchar(50)

AS

DELETE @txtTableName
GO

...all I get is an error saying that I need to declare @txtTableName.

 

[dbomrrsm]

cos you just cant do it that way.

[blue]DBomrrsm[/blue]

 

[rookery]

So let me get this right:-

I can use a variable in the following way:

CREATE PROCEDURE spTest1

@INVOICEID int

AS
DELETE FROM [tblExample1]
WHERE INVOICEID = @INVOICEID
GO

but not when I want to pass the name of the table which I want to delete all the records from like in my examples above?

If I'm correct, why so?

 

[SQLBill]

Correct. SQL Server doesn't like variables as Tablenames. You can't do:

DECLARE @tblname varchar(7)
SET @tblname = 'mytable'
SELECT *
FROM @tblname

SO it's probably the same issue with your delete.

-SQLBill

 

[rookery]

OK thanks Bill and all other contributors.

 

[rookery]

The recommended syntax still doesn't execute. This is my simple question:

Can I create a stored proc that will delete the entire contents of a table on demand? I will pass the tablename into a variable at run time.

Can this only be achieved via a function?

 

[dbomrrsm]

This works for me:

CREATE PROCEDURE spDeleteTable
@txtTableName nvarchar(50)
AS
declare @sql nvarchar (100)
set @sql = 'DELETE '+@txtTableName
exec (@sql)
--then execute as follows
exec spDeleteTable 'dbo.DemoTab1'

[blue]DBomrrsm[/blue]

 

[rookery]

Sorted! I wasn't executing it correctly. (missed off the apostrophes round the table name)

Have a star that man!

 

[dbomrrsm]

Glad to be of help.

[blue]DBomrrsm[/blue]

 

[ESquared]

But I'd like to reiterate that I think it's a horrible idea to have a stored procedure that deletes the contents of tables. At the very least you should have it check to see if the passed-in table is in a (very short) list of tables that are okay to do this to.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k