SQL 7 SPROC HELP / ASAP PLEASE

[JasonGS]

I am kind of new to SPROCs, can anyone see why won't this work?

CREATE PROCEDURE sp_emptytable
@MyTable varchar(50)
AS
DELETE FROM @MyTable WHERE id > 0

I am entering the SPROC using SQL Server Enterprise Manager.

[Mongo]

A stored procedure is a precompiled SQL statement. While SQL is a language, it is not a programming language; and you have to use valid SQL syntax. Since @MyTable is an invalid object in your database, the SQL cannot be precompiled. With MS SQL Server you can use a sexy CASE statement or a series of IF statements to create a psuedo-generic stored procedure for deleting.

Assuming MyNullTable, MyCatTable and MyDogView are valid objects in your database:

CREATE PROCEDURE sp_emptytable (@MyTable varchar(50) = null) AS
IF ( (@MyTable = null )
BEGIN
DELETE FROM MyNullTable WHERE id > 0
RETURN
END
IF ( (@MyTable = 'Cat' )
BEGIN
-- mo' betta way to empty the entire table...
TRUNCATE TABLE MyCatTable
RETURN
END
IF ( (@MyTable = 'Dog' )
DELETE FROM MyDogView WHERE id > 0
RETURN

[JasonGS]

Cool, thanks alot!

[Clunietp]

This works fine in SQL 7


CREATE PROCEDURE spu_NukeTable
@TableName varchar(200)
AS
exec('Truncate Table ' + @TableName)


I'm pretty sure you can pass table/column names as parameters to a SP as well, if I find that info I'll be sure to post it....