CODEWITHSUNDEEP
sqlsql-servervisual-studio-2010
Drazen Bjelovuk
Drazen Bjelovuk

Reputation: 5482

Incorrect syntax near the keyword 'Truncate'. Must declare scalar variable '@table'

I'm quite new to SQL and I'm not sure why the following code is producing the error in title:

CREATE PROCEDURE Truncate (@table varchar(50))
AS
BEGIN
    SET NOCOUNT ON;
    EXEC ('TRUNCATE TABLE ' + @table);
END

Upvotes: 1

Views: 4264

Answers (4)

Sami
Sami

Reputation: 8419

Use some other name for the procedure instead of Truncate e.g. use trucateMyTable

Reason:

Truncate is a key word and

A variable/procedure/function name can not be a keyword.

Upvotes: 1

codingbiz
codingbiz

Reputation: 26386

TRUNCATE is a reserved or keyword in SQL Server. Just change the name to something else. Avoid putting [ ] around it like [TRUNCATE]

Upvotes: 3

Bort
Bort

Reputation: 7638

TRUNCATE is a keyword, so you should name your procedure something else, ex:

CREATE PROCEDURE TruncateTable (@table varchar(50))
AS
BEGIN
    SET NOCOUNT ON;
    EXEC ('TRUNCATE TABLE ' + @table);
END

Upvotes: 3

John Bingham
John Bingham

Reputation: 2006

Since Truncate is a reserved word, you cannot use it in the procedure name unquoted. You can do this:

CREATE PROCEDURE dbo.[Truncate] (@table varchar(50)) 
AS BEGIN     
  SET NOCOUNT ON;     
  EXEC ('TRUNCATE TABLE ' + @table); 
END

But when you run it, you'll have to say

dbo.[Truncate] 'tablename'

Alternatively change the name of the procedure to TruncateTable:

CREATE PROCEDURE dbo.TruncateTable (@table varchar(50)) 
AS BEGIN     
  SET NOCOUNT ON;     
  EXEC ('TRUNCATE TABLE ' + @table); 
END

then you can call it as

EXEC TruncateTable 'tablename'

Upvotes: 4

Related Questions