SQL check if table exists
I have some dynamic SQL - below - what I need to do is check if the table exists in the remote db - if so use the below - else, I will use something else. The db is dynamically passed n as a param (@strDB)
set @strSQL = 'SELECT NULL, ' + CAST(@contractID as varchar(20)) + ', PATIENT_TP, CASE WHEN MAX(REIMB_TP) = ''FIXED'' THEN 4 WHEN MAX(REIMB_TP) = ''VARIABLE'' THEN 5 END,
SUM(CHARGES), MAX(BD_CONTRACT), 0, (100-MAX(BD_CONTRACT)), MAX(REIMB_TP)
FROM data8.' + @strDB + '.dbo.tblREIMBURSEMENT WHERE insurance = ' + @ID + ' GROUP BY PATIENT_TP'
ASKER
How do I check it against a database when the database name is a param being passed in? I assume that it will be some mix of dynamic sql, etc...?
ASKER
This is what I am trying - but for some reason it still tries to select against the table in question - and errors out, etc.
DECLARE @tmp0 TABLE ([reimbID] int, [contractID] varchar(50), [patientType] varchar(1),
[reimbursementMethod] int, [totalCharge] money, [chargePercentageDiscount] numeric(10,2),
[claimCap] money, [cashContributionMargin] numeric(10,2), [settlementMethod] varchar(50))
DECLARE @contractID int, @ID varchar(50)
SET @contractID = 123
DECLARE @strDB varchar(100)
declare @strSQL nvarchar(max)
SET @ID = (SELECT contractNumber FROM CM_tblContractSummary WHERE contractID = @contractID)
SET @strDB = 'dbTexasHealthResources'
set @strSQL = 'IF EXISTS (SELECT 1
FROM DATA8.' + @strDB + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE=''BASE TABLE''
AND TABLE_NAME=''tblREIMBURSEMENT'') BEGIN
SELECT NULL, ' + CAST(@contractID as varchar(20)) + ', PATIENT_TP, CASE WHEN MAX(REIMB_TP) = ''FIXED'' THEN 4 WHEN MAX(REIMB_TP) = ''VARIABLE'' THEN 5 END,
SUM(CHARGES), MAX(BD_CONTRACT), 0, (100-MAX(BD_CONTRACT)), MAX(REIMB_TP)
FROM data8.' + @strDB + '.dbo.tblREIMBURSEMENT WHERE insurance = ' + @ID + ' GROUP BY PATIENT_TP END
ELSE BEGIN
SELECT NULL, ' + CAST(@contractID as varchar(20)) + ', NULL, NULL, NULL, NULL, NULL, NULL, NULL END'
INSERT INTO @tmp0
exec sp_executesql @strSQL
SELECT * FROM @tmp0
what if you use
SET @STRsql = 'IF OBJECT_ID (N''DATA8.'' + @STRdb + ''dbo.tblREIMBURSEMENT'', N''U'') IS NOT NULL
BEGIN
.....
SET @STRsql = 'IF OBJECT_ID (N''DATA8.'' + @STRdb + ''dbo.tblREIMBURSEMENT'', N''U'') IS NOT NULL
BEGIN
.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The reason for that is you receive compilation error when the object does not exist before SQL server tries to execute your check syntax
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'