I am doing a union
of same table from different databases to get all the company details. Something like below.
CREATE PROCEDURE [dbo].[getCompanyDetails]
(@Companycode varchar(2))
AS
BEGIN
SELECT '[FirstDataBase]' DBNAME, CompanyID, CompanyName
FROM [FirstDataBase].dbo.tblCompany
WHERE CompanyID LIKE @Companycode+'%'
UNION
SELECT '[SecondDataBase]' DBNAME, CompanyID, CompanyName
FROM [SecondDataBase].dbo.tblCompany
WHERE CompanyID LIKE @Companycode+'%'
UNION
SELECT '[ThirdDataBase]' DBNAME, CompanyID, CompanyName
FROM [ThirdDataBase].dbo.tblCompany
WHERE CompanyID LIKE @Companycode+'%'
UNION
SELECT '[FourthDataBase]' DBNAME, CompanyID, CompanyName
FROM [FourthDataBase].dbo.tblCompany
WHERE CompanyID LIKE @Companycode+'%'
END
This is working fine and I am getting all company details, but I want to make sure that database is exist in the server.
Because if any one of the union database is not exist in the server then the stored procedure will fail. How to change the stored procedure so that even any of the union fails the other should work and give result?
PS: one extra question, is there any way I can union one database from another server?
Advertisement
Answer
Simplest is IF DB_ID('yourdb') IS NOT NULL
For discussion etc see How to check if a database exists in SQL Server?
One way to use this with the select statements is to use dynamic SQL to construct an SQL statement then run it e.g.,
DECLARE @CustomSQLSelect nvarchar(4000)
SET @CustomSQLSelect =
CASE WHEN DB_ID('FirstDataBase') IS NOT NULL
THEN ' UNION Select ''[FirstDataBase]'' DBNAME,CompanyID ,CompanyName
From [FirstDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
ELSE '' END
+ CASE WHEN DB_ID('SecondDataBase') IS NOT NULL
THEN ' UNION Select ''[SecondDataBase]'' DBNAME,CompanyID ,CompanyName
From [SecondDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
ELSE '' END
+ CASE WHEN DB_ID('ThirdDataBase') IS NOT NULL
THEN ' UNION Select ''[ThirdDataBase]'' DBNAME,CompanyID ,CompanyName
From [ThirdDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
ELSE '' END
+ CASE WHEN DB_ID('FourthDataBase') IS NOT NULL
THEN ' UNION Select ''[FourthDataBase]'' DBNAME,CompanyID ,CompanyName
From [FourthDataBase].dbo.tblCompany Where CompanyID like ''' + @Companycode + ''' +''%'''
ELSE '' END;
-- You may want to put in a PRINT @CustomSQLSelect to review the SQL
IF @CustomSQLSelect LIKE ' UNION %'
BEGIN
SET @CustomSQLSelect = STUFF(@CustomSQLSelect, 1, 7, ''); -- Delete the first ' UNION ' statement
EXEC (@CustomSQLSelect);
END
ELSE
BEGIN
PRINT 'No valid databases!'
END;
I think I got the apostrophes etc correct in the above SQL.
You could also just create a temporary table and fill it … this is easier to maintain in my opinion.
CREATE TABLE #Companies (
DBName nvarchar(100),
CompanyId varchar(20),
CompanyName nvarchar(100)
)
-- Note - set your nvarchar/varchars appropriately
IF DB_ID('FirstDatabase') IS NOT NULL
BEGIN
INSERT INTO #Companies (DBName, CompanyID, CompanyName)
Select '[FirstDataBase]' DBNAME,CompanyID ,CompanyName
From [FirstDataBase].dbo.tblCompany
Where CompanyID like @Companycode+'%'
END
-- Then do the same for other databases
Note that viewing your queries in SSMS may indicate errors if the databases don’t exist.
Also note that a UNION (rather than a UNION ALL) will effectively change the ‘SELECT’ statements into ‘SELECT DISTINCT’ to stop duplicates. This adds extra processing.
The temporary table version doesn’t have that. It’s up to you to add the ‘DISTINCT’ if desired.
Alternatively, if you’re happy with duplicates, or you know there are no duplicates (e.g., companyID is a PK or otherwise unique within each database) then changing the UNIONs to UNION ALL may speed up processing.
For reading data from other servers – one way is to use Linked servers. These are set up at server level I believe. You’ll need to do research to see if this is right for you.