Reputation: 941
I am working on project with multiple databases. There is a chance the database may not exists. I am checking to see if the database exists before I run query against the table. The issue is that I am still getting database "Courses" does not exit. I'd like to get rid of that message because I don't want the client to see this message.
DECLARE @dbname nvarchar(128)
SET @dbname = N'Courses'
IF EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE ('[' + name + ']' = @dbname
OR name = @dbname))
BEGIN
IF NOT EXISTS (SELECT 1 FROM Courses.INFORMATION_SCHEMA.COLUMNS C WHERE C.COLUMN_NAME IN ('isAvailableOnline') AND C.TABLE_NAME IN ('Course'))
BEGIN
ALTER TABLE Courses.dbo.Course ADD isAvailableOnline BIT NULL
END
END
Upvotes: 2
Views: 6021
Reputation: 454020
Wrap the expression using the potentially non existent database in EXEC
so it is only compiled if that branch is taken.
I used some shorter idioms for checking database and column existence. If you stick with your existing one for columns it will need to go inside the string that is EXEC
ed with the quotes doubled up to escape them.
DECLARE @dbname sysname
SET @dbname = N'Courses'
IF DB_ID(@dbname) IS NOT NULL
BEGIN
IF COL_LENGTH('Courses.dbo.Course','isAvailableOnline') IS NULL
BEGIN
EXEC('ALTER TABLE Courses.dbo.Course ADD isAvailableOnline BIT NULL')
END
END
Upvotes: 4