Creating a Stored Procedure to Truncate a Table

Question

Dear Sir / Madam,

I want to create a stored procedure to truncate a table.

I have to do this, as I have data coming into a Service Broker queue, that requires processing in different ways, based on the table name of the table that the data has come from (on a remote server).

I have tried the following code, but it doesn't work. It was hard to find a solution by searching the internet.

Could you please amend the code below, so that it will work?

CREATE PROCEDURE [dbo].[TruncateTable] @databaseName varchar(50), @tableName varchar(50)

WITH EXECUTE AS SELF

AS

USE @databaseName

GO

TRUNCATE TABLE @tableName

GO

GO

Thanks,

Damian.

Answer 1

Try this code.

CREATE PROCEDURE [dbo].[TruncateTable] @databaseName varchar(50), @tableName varchar(50)
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL='TRUNCATE TABLE ' + @databaseName + '..' + @tableName
EXEC (@SQL)

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 2

This should work

 

Create procedure trun @dbname varchar(50) , @tablename varchar(50)

as

declare @sql varchar(100)

            set @SQL = 'use ' + rtrim(@dbname) + char(13)+ ' truncate table '+ rtrim(@tablename) + ''

            execute(@Sql)

 

 

Then execute this

 

 

exec master.dbo.trun 'leks','t1'

---

Thanks, Leks

Answer 3

Add a prevention against SQL injection attack like shown here

http://www.tek-tips.com/viewthread.cfm?qid=1575213&page=1

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

also mention the default schema with default value = dbo (optional) and [] brackets in your code to avoid errors.

-------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[TruncateTable]
( @databaseName varchar(50), @tableName varchar(50),@schemaName varchar(50)='dbo')
as
DECLARE @SQL VARCHAR(2000)
SET @SQL='TRUNCATE TABLE ' + @databaseName + '.' + @schemaName + '.[' +  @tableName + ']'
exec (@SQL)

 

---

Please mark the post as answered to help others to choose the best.
chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

Answer 5

Awesome answers.

Thanks for the knowledge everyone.