Queries and views

[wbodger]

I have this view (or cte)

select protocolid, dtblname, v_name from cte

with data like

protocolid dtblname     v_name
108	tblEligibility	BASELINE_CANCER-HX_BLADDER-ONLY_CODE
108	tblEligibility	BASELINE_CANCER-HX_CHEMO-EVER_CODE
108	tblEligibility	BASELINE_CANCER-HX_CONFIRMATION_CODE
108	tblEligibility	BASELINE_CANCER-HX_GENE-THERAPY-SPECIFY_TEXT

and I want to use this data to build a looping query that would give me something like

select v_name from dbedrn(protocolid).dbo.dtblname

where I use the results from the first view/query to build the second query. Any suggestions since my ideas aren't working?

wb

 

[simian336]

Could you provide a data same for what you want the final result to be.

Simi

 

[wbodger]

Yes. As I was thinking about it this morning I thought of a better way to present it (I hope). So, I have this data (which is a list of all required fields for all forms in all of our databases)

protocolid dtblname     v_name
108	tblEligibility	BASELINE_CANCER-HX_BLADDER-ONLY_CODE
108	tblEligibility	BASELINE_CANCER-HX_CHEMO-EVER_CODE
108	tblEligibility	BASELINE_CANCER-HX_CONFIRMATION_CODE
108	tblStudyInfo    BASELINE_CANCER-HX_GENE-THERAPY-SPECIFY_TEXT

and what I want to come out of that is

if protocolid=108 then
select *
from dbMSA.tblEligibility
where [BASELINE_CANCER-HX_BLADDER-ONLY_CODE] is null
or [BASELINE_CANCER-HX_CHEMO-EVER_CODE] is null
or [BASELINE_CANCER-HX_CONFIRMATION_CODE] is null

select * from dbMSA.tblStudyInfo
where [BASELINE_CANCER-HX_GENE-THERAPY-SPECIFY_TEXT] is null
.
.
.

So, I want to build queries dependent on the protocolid and dtblname and I am looking for any null values in required fields. Does that make a little more sense?

wb

 

[lameid]

The easy thing to do assuming you are not trying to build the SQL strings and run them would to build a simple select statement that concatenates the SQL statement you want together and return it as a column. When you run the Select, then the output will be a bunch of select statements you can copy and paste and use.

To conditionally do something in a select use a Case when statement.

 

[wbodger]

I do actually need to run the script(s) as they are being built. Right now I have this view

SELECT distinct fn.PROTOCOLID, dc.DBNAME, '['+ltrim(rtrim(dep.V_NAME))+']' as v_name, dep.DTBLNAME, fn.FormID, dep.DPAGE, dep.DORDER
FROM         dbo.vwDEPAGE AS dep INNER JOIN
                      dbo.vwFormName AS fn ON dep.FORMID = fn.FormID join tblDataConnect dc
                      on fn.PROTOCOLID=dc.PROTO_ID
WHERE     (dep.DREQUIRE = 1) AND (fn.PROTOCOLID <> 0) AND (fn.PROTOCOLID <= 354)

and then this stored procedure

ALTER PROCEDURE [dbo].[FindMissingFields] 
	-- Add the parameters for the stored procedure here
	@dbIName varchar(50) 
AS

DECLARE @dbname varchar(50),
		@tblname varchar(50),
		@vname varchar(50),
		@dpage int,
		@dorder int,
		@pks varchar(4000),
		@adhoc varchar(max)

BEGIN
	SET NOCOUNT ON;

	DECLARE db_cursor CURSOR FOR 
SELECT	DISTINCT ltrim(rtrim(vw1.DBNAME)) as dbname, 
		ltrim(rtrim(vw1.DTBLNAME)) as dtblname, 
		ltrim(rtrim(vw1.V_NAME)) as v_name, 
		vw1.DPAGE, 
		vw1.DORDER,
		substring ( stuff(
                              (
                                select ' , ' + rtrim(ltrim(cast(V_NAME as varchar(max))))
                                from dbDEMAPPING.dbo.vwPR2871_TblsFlds as vw2
                                where DPAGE=0 and vw2.DTBLNAME=vw1.DTBLNAME and vw2.FormID=vw1.FormID
                                order by DPAGE, DORDER 
                                for xml path ('') ),
                                1 ,
                                2 ,
                                '') 
                                , 2 , 1000 ) as pks
from dbDEMAPPING.dbo.vwPR2871_TblsFlds as vw1
where DBNAME=@dbIName
order by dbname, dtblname, dpage, dorder	
	OPEN db_cursor  
	FETCH NEXT FROM db_cursor INTO @dbname, @tblname, @vname, @dpage, @dorder, @pks
	
	WHILE @@FETCH_STATUS = 0  
	BEGIN 
		set @adhoc = 'SELECT ' + @pks + ' from ' + @dbname + '.dbo.' + @tblname + ' where '+ @vname + ' is null'
		
		exec (@adhoc)
		
		FETCH NEXT FROM db_cursor INTO @dbname, @tblname, @vname, @dpage, @dorder, @pks  
	END

CLOSE db_cursor  
DEALLOCATE db_cursor 
END

which seems to work for the most part, but one thing I would like to do is identify in the output what db (@dbname) and table (@tblname) these are for. I tried

set @adhoc = 'SELECT "'+@tblname+'"' + @pks + ' from ' + @dbname + '.dbo.' + @tblname + ' where '+ @vname + ' is null'

but it tried to use @tblname as a column name. Is there any way, in this style, that I can use those variables (@dbname and @tblname) as constants in the query @adhoc? Thanks!

wb

 

[lameid]

execute() or sp_executesql look like the options you are looking for...

An entire article on dynamic SQL:

http://www.techrepublic.com/blog/datacenter/generate-dynamic-sql-statements-in-sql-server/306

The risk in doing something like this is always SQL injection attacks.

 

[wbodger]

This is for an internal audit, so there is no danger of SQL injection as I will be firing off the stored procedure and exec() is what you use to take a dynamic string as input and execute it, that is not the issue. It runs fine, it is just that when I run it for a certain database, the results come back with only the primary keys, nothing to identify which table they are from. I have that value in the stored procedure (@tblname), but I cannot insert it as a constant into the dynamic sql (@adhoc). So,

set @adhoc = 'SELECT "'+@tblname+'"' + @pks + ' from ' + @dbname + '.dbo.' + @tblname + ' where '+ @vname + ' is null'

gives me an error because it is trying to use @tblname as a columnname (whereas I want it as a constant).
This:

set @adhoc = 'SELECT ' + @pks + ' from ' + @dbname + '.dbo.' + @tblname + ' where '+ @vname + ' is null'

works and gives me

SELECT [STUDY_PARTICIPANT_ID] , [STUDY_PROTOCOL_INELIGIBLE_DATE] from TBLINELIGIBILITY where [BASELINE_CONSENT_GROUP_CODE] is null

but I would like to have

SELECT 'TBLINELIGIBILITY' as tablename, [STUDY_PARTICIPANT_ID] , [STUDY_PROTOCOL_INELIGIBLE_DATE] from TBLINELIGIBILITY where [BASELINE_CONSENT_GROUP_CODE] is null

where TBLINELIGIBILITY is the value in @tblname (and thus a changing value)). Does that make more sense? Again, the view and stored procedure run just fine and work properly, I am just trying to get a little more information that is not contained in the table into the final query output.

Thanks,
wb

 

[lameid]

Now I see the problem... Double quotes are not used for literals / strings at all in T-SQL. You just double up Single quotes to get one single quote embedded in a string so '' in as string becomes '... so a string that begins and ends with a single quote '''' evaluates to '

set @adhoc = 'SELECT [red]'''[/red] + @tblname + [red]''''[/red] + @pks + ' from ' + @indbname + '.dbo.' + @tblname + ' where '+ @vname + ' is null'

 

[wbodger]

That's it, thx!