Deferred name resolution)

It is often necessary to determine whether data exists during development.

For example:

If exists (select * From SYS. databases where name = 'dbnotexists ')
Begin
Use dbnotexists;
If not exists (select * From SYS. objects where object_id = object_id (n' [DBO]. [tablenotexists] ') and type in (n'u '))
Create Table tablenotexists (a int)

Select * From tablenotexists
End
Else
Begin
Create Database
[Dbnotexists] on primary (name = n' dbnotexists ', filename = n' D:/data/dbnotexists. MDF', size = 4048kb, filegrowth = 1024kb)
Log On (name = n'dbnotexists _ log', filename = n'd:/data/dbnotexists_log.ldf ', size = 4024kb, filegrowth = 10% );
Use dbnotexists;
If not exists (select * From SYS. objects where object_id = object_id (n' [DBO]. [tablenotexists] ') and type in (n'u '))
Create Table tablenotexists (a int)

Select * From tablenotexists

End

 

If the database dbnotexists exists, the tablenotexists table is created in the database,

Otherwise, create dbnotexists and then tablenotexists.

From the code point of view, there seems to be no problem.

In fact, this code is compiled only when the database dbnotexists exists.

Otherwise, an error is returned during compilation.

The error is as follows:

Database 'dbnotexists' does not exist. Make sure that the name is entered correctly.

After reading the SQL Server online documentation, you will find that latency name resolution in SQL Server only works on the table, and does not work on databases or field names,

That is to say, dbnotexists cannot delay parsing, so when the library does not exist, an error will be reported during compilation.

Solution:

1. You can consider making judgments on front-end components or applications and creating libraries and tables for response.

2. Use dynamic SQL.

For example:

If exists (select * From SYS. databases where name = 'dbnotexists ')
Begin
Execute sp_executesql n' use dbnotexists;
If not exists (select * From SYS. objects where object_id = object_id (n'' [DBO]. [tablenotexists] '') and type in (n''u ''))
Create Table tablenotexists (a int)

Select * From tablenotexists'
End
Else
Begin
Execute sp_executesql N'
Create Database
[Dbnotexists] on primary (name = n' dbnotexists '', filename = n' C:/data/dbnotexists. MDF'', size = 4048kb, filegrowth = 1024kb)
Log On (name = n' dbnotexists _ log'', filename = n' C:/data/dbnotexists_log.ldf'', size = 4024kb, filegrowth = 10% );
'

Execute sp_executesql N'
Use dbnotexists;
If not exists (select * From SYS. objects where object_id = object_id (n'' [DBO]. [tablenotexists] '') and type in (n''u ''))
Create Table tablenotexists (a int)

Select * From tablenotexists'

End