SQL Server : error assigned name to a local variable

Question

I have a problem with assigning a database name to a variable. When I do this, I get a message that there is no such database.

What am I doing wrong that this script doesn't work?

DECLARE @db_name varchar(10) 
SET @db_name = 'xxx'

ALTER TABLE [@db_name].[dbo].[Table_Name] 
    DROP CONSTRAINT [Constraint_Name]
GO

ALTER TABLE [@db_name].[dbo].[Table_Name] WITH CHECK 
    ADD CONSTRAINT [Constraint_Name] CHECK (QUERY)
GO

ALTER TABLE [@db_name].[dbo].[Table_Name] CHECK CONSTRAINT [Constraint_Name]
GO

I'm getting this error message:

Msg 2702, Level 16, State 2, Line 5
Database '@db_name' does not exist.

Msg 2702, Level 16, State 2, Line 8
Database '@db_name' does not exist.

Msg 2702, Level 16, State 2, Line 11
Database '@db_name' does not exist.

Table has name `BigBookEntry`. However, it's rather like the table is called, it doesn't matter. — Kamil M, Jul 03 '20 at 08:27

score 1 · Answer 1 · answered Jul 03 '20 at 08:28

A query - meaning all text until each GO command - is compiled before it is run.

Database names inside queries have to be be known at compile time, because the database + any table or column you specify are inspected by the query compiler to see if they are valid.

This means that Database names + Table names + Column names all cannot come from @-variables, because at compile time variables don't yet exist and have no value.

The fix is to use dynamic SQL:

DECLARE @db_name varchar(10) = 'xxx'
DECLARE @sql varchar(1000) = 'ALTER TABLE [' + @db_name + '].[dbo].[Table_Name] DROP CONSTRAINT [Constraint_Name]'

EXEC (@sql)

score 1 · Accepted Answer · answered Jul 03 '20 at 08:32

let's say for example that you have a table created like this:

create table xxx(id int
                 CONSTRAINT AK_TransactionID UNIQUE(id))

Then you will need to cerate two variables(one for the object name and one for the query to be executed...):

DECLARE @db_name varchar(10) ;
DECLARE @query nvarchar(500) ;

Set them to the values you need:

SET @db_name = 'xxx';
set @query = 'ALTER TABLE ['+ @db_name +' ] DROP CONSTRAINT [AK_TransactionID]';

And then execute the query:

EXEC sp_executesql @query;

Here is a demo

score 1 · Answer 3 · edited Oct 13 '20 at 00:50

Unfortunately in SQL Server, you cannot pass schema, database, table or column names as a parameter - if you want to do this, you must use dynamic SQL. You could do the below to achieve this:

DECLARE @db_name VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
SET @db_name = [xxx]

SET @SQL = 'ALTER TABLE' + @db_name+ '.[dbo].[Table_Name] DROP CONSTRAINT [Constraint_Name]'
EXEC (@SQL)

SET @SQL = 'ALTER TABLE' + @db_name + '.[dbo].[Table_Name]  WITH CHECK ADD  CONSTRAINT [Constraint_Name] CHECK  (QUERY)'
EXEC (@SQL)

SET @SQL = 'ALTER TABLE' + @db_name + '.[dbo].[Table_Name] CHECK CONSTRAINT [Constraint_Name]'
EXEC (@SQL)

SQL Server : error assigned name to a local variable

3 Answers3