Creating a Stored Procedure to Truncate a Table

Question

_{Thursday, March 4, 2010 11:25 PM}

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.

All replies (5)

_{Friday, March 5, 2010 12:49 AM ✅Answered}

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!

---

_{Friday, March 5, 2010 12:52 AM ✅Answered}

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

---

_{Friday, March 5, 2010 5:06 AM | 1 vote}

Add a prevention against SQL injection attack like shown here

http://www.tek-tips.com/viewthread.cfm?qid=1575213&page=1Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

---

_{Friday, March 5, 2010 8:42 AM}

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)

---

_{Saturday, March 6, 2010 4:45 AM}

Awesome answers.

Thanks for the knowledge everyone.