开发中经常需要做数据是否存在的判断
例如下例:
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
如果存在数据库DBNotExists则在该库中创建表TableNotExists ,
否则先创建DBNotExists然后再创建表TableNotExists。
从代码看来,似乎也没有什么问题。
实际上,该段代码仅在数据库DBNotExists存在时才会编译通过。
否则,该语句会在编译中报错。
错误如下:
Database 'DBNotExists' does not exist. Make sure that the name is entered correctly.
查阅SQL Server联机文档会发现,SQL Server中的延迟名称解析仅对表起作用,对于,数据库,字段名均不起作用,
也就说对于DBNotExists不能延迟解析,所以当该库不存在是,编译时会报出错误。
解决办法:
1. 可以考虑在前台组件或者应用程序中进行判断并创建响应的库和表。
2.使用动态SQL。
例如:
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