Using a variable in a Truncate table command.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using a variable in a Truncate table command.

Author

Topic

jgsteeler
Starting Member

12 Posts

Posted - 2005-04-11 : 12:22:12

I am trying to write a small stored procedure that accepts a table name as a paramater then uses the paramater as a variable and truncate the data that is in the table.

Here is the procedure I have.


CREATE PROCEDURE [dbo].[qryTruncateTable]
@txtTableName nvarchar(25)

AS

TRUNCATE TABLE @txtTableName
GO

I am getting this error when I try to parse the query.


Server: Msg 170, Level 15, State 1, Procedure qryTruncateTable, Line 7
Line 7: Incorrect syntax near '@txtTableName'.

Can anybody help me out with the syntax i need to be able to perform this action?

Thanks

Always look on the bright side of life, ta dum, ta dum.

Monthy Python, The Life of Brian

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-11 : 12:51:37

Try this:

CREATE PROCEDURE [dbo].[qryTruncateTable]
@txtTableName nvarchar(25)
AS

DECLARE @SQL VARCHAR(100)

SET @SQL = 'TRUNCATE TABLE ' + @txtTableName
EXECUTE (@SQL)

GO

jgsteeler
Starting Member

12 Posts

Posted - 2005-04-11 : 14:07:26

Thanks that will work great!

Always look on the bright side of life, ta dum, ta dum.

Monthy Python, The Life of Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources