CODEWITHSUNDEEP
sqlsql-server
Ashok kumar
Ashok kumar

Reputation: 1611

Syntax error in stored procedure

I am trying to write one small stored procedure to clean up database programmatically.

For this,

First, I am dropping all Foreign key constraints

Second, I am dropping all primary key constraints,

Third, I am dropping all the tables.

I have written the following code to do above three steps (third step not yet started)

CREATE PROCEDURE usp_CleanupDB AS
BEGIN
--Begin: Code to drop FOREIGN KEY CONSTRAINTS in the database
DECLARE @ForeignKeyConstraint AS VARCHAR(100)
DECLARE @ForeignKeyContainedTableName AS VARCHAR(100)
DECLARE @ForeignKeyConstraintsTableCursor AS CURSOR
SET @ForeignKeyConstraintsTableCursor = CURSOR FOR
SELECT ForeignKeyName, TableName FROM dbo.GetDBForeignKeyConstraints()
OPEN @ForeignKeyConstraintsTableCursor
FETCH NEXT FROM @ForeignKeyConstraintsTableCursor INTO @ForeignKeyConstraint, @ForeignKeyContainedTableName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Drop FOREIGN KEY Constraint
    ALTER TABLE @ForeignKeyContainedTableName DROP CONSTRAINT @ForeignKeyConstraint
    FETCH NEXT FROM @ForeignKeyConstraintsTableCursor into @ForeignKeyConstraint, @ForeignKeyContainedTableName
END
CLOSE @ForeignKeyConstraintsTableCursor
DEALLOCATE @ForeignKeyConstraintsTableCursor
--End: Code to drop FOREIGN KEY CONSTRAINTS in the database
--Begin: Code to drop PRIMARY KEY CONSTRAINTS in the database
DECLARE @PrimaryKeyConstraint AS VARCHAR(100)
DECLARE @PrimaryKeyContainedTableName AS VARCHAR(100)
DECLARE @PrimaryKeyConstraintsTableCursor AS CURSOR
SET @PrimaryKeyConstraintsTableCursor = CURSOR FOR
SELECT PrimaryKeyName, TableName FROM dbo.GetDBPrimaryKeyConstraints()
OPEN @PrimaryKeyConstraintsTableCursor
FETCH NEXT FROM @PrimaryKeyConstraintsTableCursor INTO @PrimaryKeyConstraint, @PrimaryKeyContainedTableName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Drop PRIMARY KEY Constraint
    ALTER TABLE @PrimaryKeyContainedTableName DROP CONSTRAINT @PrimaryKeyConstraint
    FETCH NEXT FROM @PrimaryKeyConstraintsTableCursor INTO @PrimaryKeyConstraint, @PrimaryKeyContainedTableName
END
--End: Code to drop PRIMARY KEY CONSTRAINTS in the database
END

I am getting the following error:

Msg 102, Level 15, State 1, Procedure usp_CleanupDB, Line 15
Incorrect syntax near '@ForeignKeyContainedTableName'.
Msg 102, Level 15, State 1, Procedure usp_CleanupDB, Line 33
Incorrect syntax near '@PrimaryKeyContainedTableName'.

Can anybody please tell how to solve the problem?

Upvotes: 0

Views: 105

Answers (2)

Elias Hossain
Elias Hossain

Reputation: 4469

Would you please use below way, also declare your variable at the top so that each variable is available to everywhere.

EXECUTE('ALTER TABLE ' + @PrimaryKeyContainedTableName + ' DROP CONSTRAINT '+ @PrimaryKeyConstraint)

Upvotes: 1

Jan
Jan

Reputation: 16032

You can't use variables for object names in DDL commands like ALTER TABLE.

Upvotes: 2

Related Questions