I am kind of new to SPROCs, can anyone see why won't this work?
I am entering the SPROC using SQL Server Enterprise Manager.Code:CREATE PROCEDURE sp_emptytable
@MyTable varchar(50)
AS
DELETE FROM @MyTable WHERE id > 0
Printable View
I am kind of new to SPROCs, can anyone see why won't this work?
I am entering the SPROC using SQL Server Enterprise Manager.Code:CREATE PROCEDURE sp_emptytable
@MyTable varchar(50)
AS
DELETE FROM @MyTable WHERE id > 0
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:
Code: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
Cool, thanks alot!
This works fine in SQL 7
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....Code:CREATE PROCEDURE spu_NukeTable
@TableName varchar(200)
AS
exec('Truncate Table ' + @TableName)