Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CTEs adn dynamic db and table names

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
US
I have this cte
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
)
which gives me data like

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
 
You would have to use Dynamic SQL.. You would have to generate a string with the info from the CTE you created and then use sp_executesql
 
So, what am I doing wrong here?

Code:
use db1
GO

DECLARE @dbname as varchar(50)
DECLARE @query as Varchar(max)

select distinct @dbname = dbname
from db1.dbo.vwRequiredFieldsAllDBs
where dbname = 'dbBreast';

DECLARE @dtblname as varchar(100)

set @query = 'SELECT distinct @dtblname = dtblname FROM db1.dbo.vwRequiredFieldsAllDBs where dbname = '''+@dbname +''''
EXEC(@query)

I am getting the error

Code:
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@dtblname".

I know that the variable scope ends with a GO statement, does the EXEC() do the same thing? But this error is before I try to do a third query (not shown, but after the first EXEC()).

Thanks!
wb
 
Wait, scratch that. I think a different approach would be better, but... What I have is a view where I have multiple database names, with multiple table names within each database and then multiple field names within each table. So, I was thinking of something like

Code:
use dbDEMAPPING
GO

DECLARE @dbname as varchar(50)
DECLARE @dtblname as varchar(100)
DECLARE @v_name as varchar(100)
DECLARE @reqfields as varchar(max)
DECLARE @query as Varchar(max)

select distinct @dbname = dbname, @dtblname = dtblname, @vname = v_name
from dbDEMApping.dbo.vwEDRNRequiredFieldsAllDBs

@reqfields = @v_name(1)+' is null or '+@v_name(2)+' is null or '+ etc...

set @query = 'SELECT * FROM '+@dbname+'.dbo.'+@dtblname+' where '+@reqfields
EXEC(@query)

But I know that won't work. The view this comes from has 4066 records, so I will definitely go over the size limitations of varchar(max) if I try to do all of the tables at once. So, any thoughts on how I can loop through the consecutive fields?

wb
 
I would loop through your table, create the sql statment and then execute it.
 
Since this is for an audit and not something that will happen often, I used (gasp) nested cursors to create a new table and then I have a script to create the desired output with a comma separated list.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top