I have this cte
which gives me data like
Now, I need to go through the result set from this cte and query by dbname and tablename any records that have a null value in fieldname. So, I would search through db1.TBLCASECLIN where SPECIMEN_BREAST_PATHNSTAGE_CODE or STUDY_CONSENT_DATE is null. However, SQL Server does not like any attempts that I have made at doing this and it seems to me that I cannot have database or table names dynamic in my queries. So, any thoughts on how best to approach this?
Thanks,
wb
Code:
with cte as
(
SELECT DISTINCT ltrim(rtrim(vw1.DBNAME)) as dbname,
ltrim(rtrim(vw1.DTBLNAME)) as dtblname,
ltrim(rtrim(vw1.V_NAME)) as v_name,
substring ( stuff(
(
select ' , ' + cast(V_NAME as varchar(max))
from dbname.dbo.vwPR2871_TblsFlds as vw2
where DPAGE=0 and vw2.DTBLNAME=vw1.DTBLNAME
order by DPAGE, DORDER
for xml path ('') ),
1 ,
2 ,
'')
, 2 , 1000 ) as pks
from dbDEMAPPING.dbo.vwPR2871_TblsFlds as vw1
)
Code:
dbname tblname fieldname
db1 tblBreastParticipantSub [PPT_FAMILYCANCERHX_AGEDX_VALUE]
db1 TBLBRSPARTICIPANT [STUDY_PARTICIPANT-ID_SITE_TEXT]
db1 TBLBRSPARTICIPANT [STUDY_PROTOCOL_BRFFINALGROUP_CODE]
db1 TBLBRSPARTICIPANT [STUDY_SITE_ID]
db1 TBLCASECLIN [SPECIMEN_BREAST_PATHNSTAGE_CODE]
db1 TBLCASECLIN [STUDY_CONSENT_DATE]
db1 TBLINELIGIBLITY [STUDY_PARTICIPANT-ID_SITE_TEXT]
db1 TBLNORMALCTRLCLIN [STUDY_CONSENT_DATE]
db1 TBLNORMALCTRLCLIN [STUDY_PROTOCOL_GROUP_CODE]
db2 tblVTreatment [PROC_LIVER_TACEYN_CODE]
db3 TBLADVERSEEVENTS [PPT_MED-HX_ADVERSEEVENT_TEXT]
db3 TBLBASEIMAGBONESCAN [STUDY_PARTICIPANT_COORDGENERATED_ID]
Now, I need to go through the result set from this cte and query by dbname and tablename any records that have a null value in fieldname. So, I would search through db1.TBLCASECLIN where SPECIMEN_BREAST_PATHNSTAGE_CODE or STUDY_CONSENT_DATE is null. However, SQL Server does not like any attempts that I have made at doing this and it seems to me that I cannot have database or table names dynamic in my queries. So, any thoughts on how best to approach this?
Thanks,
wb