DECLARE NonReportable CURSOR FOR

SELECT  PSME.Import_NonReportable.*
FROM    PSME.Import_NonReportable

OPEN NonReportable

FETCH NEXT FROM NonReportable INTO @reporting_date, @team, @process, @count, @timing, @percentage,
						 @L1, @L2, @L3, @L4, @L1_exclude, @L2_exclude, @L3_exclude,
						 @L4_exclude, @field1, @value1, @field2, @value2, @field3, @value3,
						 @field1_exclude, @value1_exclude, @process_type

WHILE @@FETCH_STATUS = 0
	BEGIN
		--build the criteria part of query
		SET @where_string = '(L1 <> ''Region1'' AND L1 <> ''Region2'') 
			AND (L2 <> ''Widget1'' AND L2 <> ''Widget2'') AND (L3 <> ''Widget3'')'
							
		IF @L1 <> 'All' AND @L1 <> '' AND NOT(@L1 IS NULL)
			SET @where_string = @where_string + ' AND L1 = ''' + @L1 + ''''
		
		IF @L2 <> '' AND NOT(@L2 IS NULL)
			SET @where_string = @where_string + ' AND L2 = ''' + @L2 + ''''
		
		IF @L3 <> '' AND NOT(@L3 IS NULL)
			SET @where_string = @where_string + ' AND L3 = ''' + @L3 + ''''
		
		IF @L4 <> '' AND NOT(@L4 IS NULL)
			SET @where_string = @where_string + ' AND L4 = ''' + @L4 + ''''
			
		IF @L1_exclude <> '' AND NOT(@L1_exclude IS NULL)
			SET @where_string = @where_string + ' AND L1 <> ''' + @L1_exclude + ''''
		
		IF @L2_exclude <> '' AND NOT(@L2_exclude IS NULL)
			SET @where_string = @where_string + ' AND L2 <> ''' + @L2_exclude + ''''
		
		IF @L3_exclude <> '' AND NOT(@L3_exclude IS NULL)
			SET @where_string = @where_string + ' AND L3 <> ''' + @L3_exclude + ''''
		
		IF @L4_exclude <> '' AND NOT(@L4_exclude IS NULL)
			SET @where_string = @where_string + ' AND L4 <> ''' + @L4_exclude + ''''
		
		IF @field1 <> '' AND NOT(@field1 IS NULL)
			SET @where_string = @where_string + ' AND [' + @field1 + '] = ''' + @value1 + ''''
			
		IF @field2 <> '' AND NOT(@field2 IS NULL)
			SET @where_string = @where_string + ' AND [' + @field2 + '] = ''' + @value2 + ''''

		IF @field1_exclude <> '' AND NOT(@field1_exclude IS NULL)
			SET @where_string = @where_string + ' AND [' + @field1_exclude + '] <> ''' + @value1_exclude + ''''
		
		--insert results into NonReportable history
		SELECT @query_string = 'INSERT INTO PSME.History_NonReportable ' +
			'SELECT ''' + @reporting_date + ''', ''' + @process + ''', ''0'', 
			''0'', NULL, NULL, NULL,''' +
			@team + ''', Idms_Region, PMS_System,
			DIS_Ind, Bus_Code, L1, L2, L3, L4, Business_Segment, Inhouse,
			NULL, Operation, Program, Program_Segment, Program_Subsegment,
			Territory, Location, Region, Orig_Prod_Type, Curr_Prod_Type, Curr_Prod_Category,
			Syn_Sec, NULL, NULL, Risk_Segment, Count(AcctSchd), ''' + 
			@process_type + ''' 
			FROM PSME.Reporting_AcctWalk_CurrentMonth 
			WHERE ' + @where_string +
			' GROUP BY Idms_Region, PMS_System,
			DIS_Ind, Bus_Code, L1, L2, L3, L4, Business_Segment, Inhouse,
			Operation, Program, Program_Segment, Program_Subsegment,
			Territory, Location, Region, Orig_Prod_Type, Curr_Prod_Type, Curr_Prod_Category,
			Syn_Sec, Risk_Segment' 	

		EXEC (@query_string)
		
		--get total count of accounts for timing
		SELECT	@totalcount = COUNT(CountOfAcctSchd) 
		FROM	PSME.History_NonReportable 
		WHERE	CountTiming = 0
			
		--update count and timing
		UPDATE	PSME.History_NonReportable
		SET	CountTiming = @count * @percentage * CountOfAcctSchd / @totalCount,
			Time_Per = @count * @timing * @percentage * CountOfAcctSchd / @totalCount
		FROM	PSME.History_NonReportable
		WHERE	CountTiming = 0
		
		--fetch next from nonreportable
	FETCH NEXT FROM NonReportable INTO @reporting_date, @team, @process, @count, @timing, @percentage,
						 @L1, @L2, @L3, @L4, @L1_exclude, @L2_exclude, @L3_exclude,
						 @L4_exclude, @field1, @value1, @field2, @value2, @field3, @value3,
						 @field1_exclude, @value1_exclude, @process_type
	END

CLOSE NonReportable
DEALLOCATE NonReportable

Create proc pNastyAssDynamicQuery (
 @reporting_date
, @team varchar(20)
, @process varchar(20)
, @count varchar(20)
, @timing varchar(20)
, @percentage varchar(20)
, @L1 varchar(20)
, @L2 varchar(20)
, @L3 varchar(20)
, @L4 varchar(20)
, @L1_exclude varchar(20)
, @L2_exclude varchar(20)
, @L3_exclude varchar(20)
, @L4_exclude varchar(20)
, @field1 varchar(20)
, @value1 varchar(20)
, @field2 varchar(20)
, @value2 varchar(20)
, @field3 varchar(20)
, @value3 varchar(20)
, @field1_exclude varchar(20)
, @value1_exclude varchar(20)
, @process_type  varchar(20)
)
AS

Declare @Where_Clause varchar(8000)

Select @Where_Clause = ' 1=1' +
 'L1 not in (''Region1'',''Region2'')  +
	AND L2 not in(''Widget1'',''Widget2'')  +
	AND L3 != ''Widget3'' '

IF NOT isNull(@L1,'') and @L1 != 'All'
	Select @Where_Clause = 'AND ' + @Where_Clause + char(39) + @L1 + char(39)

IF NOT isNull(@L2,'') 
	Select @Where_Clause = 'AND ' + @Where_Clause + char(39) + @L2 + char(39)

---etc


SELECT @query_string = 'INSERT INTO PSME.History_NonReportable ' +
			'SELECT ''' + @reporting_date + ''', ''' + @process + ''', ''0'', 
			''0'', NULL, NULL, NULL,''' +
			@team + ''', Idms_Region, PMS_System,
			DIS_Ind, Bus_Code, L1, L2, L3, L4, Business_Segment, Inhouse,
			NULL, Operation, Program, Program_Segment, Program_Subsegment,
			Territory, Location, Region, Orig_Prod_Type, Curr_Prod_Type, Curr_Prod_Category,
			Syn_Sec, NULL, NULL, Risk_Segment, Count(AcctSchd), ''' + 
			@process_type + ''' 
			FROM PSME.Reporting_AcctWalk_CurrentMonth 
			WHERE ' + @where_string +
			' GROUP BY Idms_Region, PMS_System,
			DIS_Ind, Bus_Code, L1, L2, L3, L4, Business_Segment, Inhouse,
			Operation, Program, Program_Segment, Program_Subsegment,
			Territory, Location, Region, Orig_Prod_Type, Curr_Prod_Type, Curr_Prod_Category,
			Syn_Sec, Risk_Segment' 	

Poor planning on your part does not constitute an emergency on my part.

EXEC pNastyAssDynamicQuery @blah, @blah, @blah, ...

quote:

---

And now that you have me thinking in that direction and I stare at my code to call queries is it by chance "cmd.CommandText = whateverYouWantToDoInsteadOfOnlySQL"?? Huge face to palm action if it's that simple.

---

Poor planning on your part does not constitute an emergency on my part.

Sub CallProcExample()
    'create empty parameters
    With cmd
        .Parameters.Append .CreateParameter("@reporting_date", adDBDate, adParamInput, , "1/1/2001")
        .Parameters.Append .CreateParameter("@team", adWChar, adParamInput, 50, "")
        .Parameters.Append .CreateParameter("@process", adWChar, adParamInput, 150, "")
        .Parameters.Append .CreateParameter("@process_type", adWChar, adParamInput, 50, "")
        .Parameters.Append .CreateParameter("@count", adDouble, adParamInput, , 0)
        'etc. etc.
    End With
    
    'set values for parameters
    Do Until Cells(row, 1).value = ""
        reportDate = Cells(row, dateCol).value
        Set param = cmd.Parameters.Item("@reporting_date")
        param.value = reportDate
        
        team = Cells(row, teamCol).value
        Set param = cmd.Parameters.Item("@team")
        param.value = team
        
        process = Cells(row, processCol).value
        Set param = cmd.Parameters.Item("@process")
        param.value = process
        
        processType = Cells(row, processTypeCol).value
        Set param = cmd.Parameters.Item("@process_type")
        param.value = processType
        
        count = Cells(row, countCol).value
        Set param = cmd.Parameters.Item("@count")
        param.value = count
        
        'etc. etc.
    
        procString = "dbo.Update_NonReportable_Test"

        Call ExecuteProcExample(procString)
        
        row = row + 1
    Loop

End Sub
Sub ExecuteProcExample(procedureName As String)
    Set cmd.ActiveConnection = cn
    cmd.CommandText = procedureName
    cmd.CommandType = adCmdStoredProc
    cmd.Execute
End Sub