Usually, no, if all the appropriate criteria were applied in the SQL that inserted into it. But sometimes, depending on what you're doing, you might need to use a where clause.
-----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 16:23 To: SQL Subject: RE: Concatenation assistance... Do I need a where clause when querying back out of the #Temp table? -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 3:56 PM To: SQL Subject: RE: Concatenation assistance... Here is a snippet that hopefully you can morph into what you need: Create procedure blah blah blah -- whole bunch of crap here that you had going -- OK, here we go, now. -- Create a temp table to hold results -- (adjust the columns to match your select clause) Create table #Temp ( cyb_record_num int, cyb_course_code int, cyb_course_grade varchar(255), ... ColumnN [type] ) -- create SQL string declare @SQL varchar(8000) set @SQL = 'insert into #Temp ( cyb_record_num, cyb_course_code, cyb_course_grade, ..., ColumnN ) select cyb_record_num, cyb_course_code, cyb_course_grade, ..., ColumnN From blah blah inner join blah on blah... Where blah blah blah and x.cyb_record_num NOT IN (' + @CleanedExcludeListB + ') -- Print/run the SQL string to store data in temp table print @SQL execute (@SQL) -- create the cursor against our temp table declare cursor MyCursor select MyField from #Temp where blah blah blah -- Now run with your cursor and do whatever you have to do -- the rest of your SP follows here END That's basically it. So, to recap, you create a temp table that has the exact column names/types that your select normally would have had, then you alter your SQL to insert the rows into that temp table that it was selecting before, then you build your cursor off of the temp table. I know, I know. Clear as mud. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 15:48 To: SQL Subject: RE: Concatenation assistance... How do I do that? Isnt the only way to get return data from a multiple record return, a cursor??? Can you explain a bit more? I dont quite get you. Thanks Tim Brad -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 3:30 PM To: SQL Subject: RE: Concatenation assistance... No. (or at least not that I know of) But, you can pre-create a temp table, then write your dynamic SQL to insert rows into that temp table, execute that sql, then create your cursor to do your dirty work after that. -----Original Message----- From: Bradford T Comer [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2003 15:22 To: SQL Subject: RE: Concatenation assistance... Quick question first, I might have it whipped; can EXEC return a cursor as output? when executing Dynamic SQL? Here is my entire script: the errors I am getting now are below it! -- START SQL -- declareNewRecordListA - Registered but not completed Course DECLARE @NewRecordIDListA varchar(8000) -- setDefault SET @NewRecordIDListA = '' -- declareNewRecordListB - Completed Course DECLARE @NewRecordIDListB varchar(8000) -- setDefault SET @NewRecordIDListB = '' -- declareMyRowCounterA DECLARE @MyRowCounterA int -- default to zero SET @MyRowCounterA = 0 -- declareMyRowCounterB DECLARE @MyRowCounterB int -- default to zero SET @MyRowCounterB = 0 -- getExcludeListA - these are users whom signed up but didnt finish course DECLARE @tmpExcludeListA varchar(8000); SET @tmpExcludeListA = (SELECT ExcludeListA FROM Custom_NAMBDataDumpData WHERE ID = 1); PRINT @tmpExcludeListA -- getExcludeListB - these are users whom have completed the course DECLARE @tmpExcludeListB varchar(8000); SET @tmpExcludeListB = (SELECT ExcludeListB FROM Custom_NAMBDataDumpData WHERE ID = 1); PRINT @tmpExcludeListB -- declareFetchReturnVariables DECLARE @cyb_record_num varchar(25), @cyb_course_code varchar(100), @cyb_course_grade varchar(100), @cyb_student_id varchar(25), @cyb_profile_email varchar(300), @cyb_profile_id_number varchar(111), @cyb_profile_org varchar(400), @cyb_profile_addr1 varchar(400), @cyb_profile_addr2 varchar(400), @cyb_profile_city varchar(400), @cyb_profile_state varchar(400), @cyb_profile_zip varchar(400), @cyb_profile_country varchar(400), @cyb_course_price money, @cyb_course_complete_date datetime, @cyb_billing_first_name varchar(400), @cyb_billing_last_name varchar(400), @cyb_order_addr1 varchar(400), @cyb_order_city varchar(400), @cyb_order_state varchar(400), @cyb_order_zip varchar(400), @cyb_order_phone varchar(300), @cyb_order_email varchar(300), @cyb_order_pay_method varchar(400), @cyb_order_cc_num varchar(150), @cyb_order_cc_expire varchar(150), @cyb_order_datetime datetime -- decalareCursor DECLARE @MyCursor CURSOR -- cleanUpBListAddApostrophes DECLARE @CleanedExcludeListB varchar(8000) SET @CleanedExcludeListB = '' DECLARE @tmpDelim varchar(15) SET @tmpDelim = CHAR(39) + CHAR(44) + CHAR(39) IF (LEN(RTRIM(@tmpExcludeListB)) > 0) BEGIN SET @CleanedExcludeListB = REPLACE(@tmpExcludeListB, CHAR(44), @tmpDelim) END ELSE BEGIN SET @CleanedExcludeListB = '' END -- finalCleanup SET @CleanedExcludeListB = CHAR(39) + @CleanedExcludeListB + CHAR(39) -- DEBUG: cleanedListB PRINT @CleanedExcludeListB DECLARE @MySQL varchar(8000) SET @MySQL = 'DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT x.cyb_record_num, x.cyb_course_code, x.cyb_course_grade, y.cyb_student_id, y.cyb_profile_email, y.cyb_profile_id_number, y.cyb_profile_org, y.cyb_profile_addr1, y.cyb_profile_addr2, y.cyb_profile_city, y.cyb_profile_state, y.cyb_profile_zip, y.cyb_profile_country, a.cyb_course_price, z.cyb_course_complete_date, (SELECT TOP 1 tbl.cyb_order_first_name FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_billing_first_name, (SELECT TOP 1 tbl.cyb_order_last_name FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_billing_last_name, (SELECT TOP 1 tbl.cyb_order_addr1 FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_addr1, (SELECT TOP 1 tbl.cyb_order_city FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_city, (SELECT TOP 1 tbl.cyb_order_state FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_state, (SELECT TOP 1 tbl.cyb_order_zip FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_zip, (SELECT TOP 1 tbl.cyb_order_phone FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_phone, (SELECT TOP 1 tbl.cyb_order_email FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_email, (SELECT TOP 1 tbl.cyb_order_pay_method FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_pay_method, (SELECT TOP 1 RIGHT(tbl.cyb_order_cc_num, 4) FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_cc_num, (SELECT TOP 1 tbl.cyb_order_cc_expire FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_cc_expire, (SELECT TOP 1 tbl.cyb_order_datetime FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_datetime FROM cyb_grade_book_mast x INNER JOIN cyb_student_profile_mast y ON x.cyb_student_id = y.cyb_student_id INNER JOIN cyb_required_course_mast z ON x.cyb_student_id = z.cyb_student_id INNER JOIN cyb_course_mast a ON x.cyb_course_code = a.cyb_course_code WHERE ((y.cyb_profile_id_number <> ''000000-00'') AND ((x.cyb_course_code = ''NAMB001M'' OR x.cyb_course_code = ''NAMB001NM'') OR (x.cyb_course_code = ''NAMB002M'' OR x.cyb_course_code = ''NAMB002NM'') OR (x.cyb_course_code = ''NAMB003M'' OR x.cyb_course_code = ''NAMB003NM'')) AND (x.variable_switch_1 <> ''M'') AND (x.cyb_org_id = 11) AND (z.cyb_course_code LIKE x.cyb_course_code) AND (z.cyb_org_id = 11) AND (x.cyb_record_num NOT IN (' + @CleanedExcludeListB + ')) ) ORDER BY x.cyb_course_code ASC OPEN @MyCursor ' PRINT @MySQL EXEC (@MySQL) -- EXEC [_MyCursorX] @MySQL = @MySQL, @spExcludeListB = @CleanedExcludeListB, @MyCursor = @MyCursor OUTPUT -- fetchNextRow FETCH NEXT FROM @MyCursor INTO @cyb_record_num, @cyb_course_code, @cyb_course_grade, @cyb_student_id, @cyb_profile_email, @cyb_profile_id_number, @cyb_profile_org, @cyb_profile_addr1, @cyb_profile_addr2, @cyb_profile_city, @cyb_profile_state, @cyb_profile_zip, @cyb_profile_country, @cyb_course_price, @cyb_course_complete_date, @cyb_billing_first_name, @cyb_billing_last_name, @cyb_order_addr1, @cyb_order_city, @cyb_order_state, @cyb_order_zip, @cyb_order_phone, @cyb_order_email, @cyb_order_pay_method, @cyb_order_cc_num, @cyb_order_cc_expire, @cyb_order_datetime -- loopCursor WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @@CURSOR_ROWS /* -- DEBUG: displayRowData PRINT @cyb_record_num + '~' + @cyb_course_code + '~' + @cyb_course_grade + '~' + @cyb_student_id + '~' + @cyb_profile_email + '~' + @cyb_profile_id_number + '~' + @cyb_profile_org + '~' + @cyb_profile_addr1 + '~' + @cyb_profile_addr2 + '~' + @cyb_profile_city + '~' + @cyb_profile_state + '~' + @cyb_profile_zip + '~' + @cyb_profile_country + '~' + CAST(@cyb_course_price AS VARCHAR) + '~' + CAST(@cyb_course_complete_date AS varchar(44)) + '~' + @cyb_billing_first_name + '~' + @cyb_billing_last_name + '~' + @cyb_order_addr1 + '~' + @cyb_order_city + '~' + @cyb_order_state + '~' + @cyb_order_zip + '~' + @cyb_order_phone + '~' + @cyb_order_email + '~' + @cyb_order_pay_method + '~' + @cyb_order_cc_num + '~' + @cyb_order_cc_expire + '~' + CAST(@cyb_order_datetime AS varchar(44)) -- DEBUG: listComparision PRINT ',' + RTRIM(@tmpExcludeListA) + ',' PRINT '%,' + RTRIM(CAST(@cyb_record_num AS VARCHAR(25))) + ',%' */ -- Newly Registered User; A list; irrelevant if they completed course or not, since all data is written IF NOT ((',' + RTRIM(@tmpExcludeListA) + ',') LIKE ('%,' + RTRIM(CAST(@cyb_record_num AS VARCHAR(25))) + ',%')) BEGIN -- insertRecord -- appendIDTo @NewRecordIDListA IF LEN(RTRIM(@NewRecordIDListA)) > 0 BEGIN SET @NewRecordIDListA = @NewRecordIDListA + ',' + CAST(@cyb_record_num AS VARCHAR(25)) END ELSE SET @NewRecordIDListA = CAST(@cyb_record_num AS VARCHAR(25)) -- incrementMyRowCounterA SET @MyRowCounterA = @MyRowCounterA + 1 END ELSE -- Registered User, check to see if they have finished the course; if TRUE add to B list BEGIN IF (RTRIM(@cyb_course_grade) <> 0) BEGIN -- appendIDTo @NewRecordIDListB IF LEN(RTRIM(@NewRecordIDListB)) > 0 BEGIN SET @NewRecordIDListB = @NewRecordIDListB + ',' + CAST(@cyb_record_num AS VARCHAR(25)) END ELSE SET @NewRecordIDListB = CAST(@cyb_record_num AS VARCHAR(25)) -- incrementMyRowCounter SET @MyRowCounterB = @MyRowCounterB + 1 END END -- getNextRow FETCH NEXT FROM @MyCursor INTO @cyb_record_num, @cyb_course_code, @cyb_course_grade, @cyb_student_id, @cyb_profile_email, @cyb_profile_id_number, @cyb_profile_org, @cyb_profile_addr1, @cyb_profile_addr2, @cyb_profile_city, @cyb_profile_state, @cyb_profile_zip, @cyb_profile_country, @cyb_course_price, @cyb_course_complete_date, @cyb_billing_first_name, @cyb_billing_last_name, @cyb_order_addr1, @cyb_order_city, @cyb_order_state, @cyb_order_zip, @cyb_order_phone, @cyb_order_email, @cyb_order_pay_method, @cyb_order_cc_num, @cyb_order_cc_expire, @cyb_order_datetime -- endLoopCursor END -- closeCursor CLOSE @MyCursor -- deallocateCursor DEALLOCATE @MyCursor -- updateExcludeListA & B with NewRecordIDListAA OR B PRINT 'A Data' PRINT @NewRecordIDListA PRINT @MyRowCounterA PRINT '' PRINT 'B Data' PRINT @NewRecordIDListB PRINT @MyRowCounterB GO -- END SQL ----------- SQL DEBUGGIN OUTPUT ----------- 98875,98876 99720,99000,98987,99117 DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT x.cyb_record_num, x.cyb_course_code, x.cyb_course_grade, y.cyb_student_id, y.cyb_profile_email, y.cyb_profile_id_number, y.cyb_profile_org, y.cyb_profile_addr1, y.cyb_profile_addr2, y.cyb_profile_city, y.cyb_profile_state, y.cyb_profile_zip, y.cyb_profile_country, a.cyb_course_price, z.cyb_course_complete_date, (SELECT TOP 1 tbl.cyb_order_first_name FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_billing_first_name, (SELECT TOP 1 tbl.cyb_order_last_name FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_billing_last_name, (SELECT TOP 1 tbl.cyb_order_addr1 FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_addr1, (SELECT TOP 1 tbl.cyb_order_city FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_city, (SELECT TOP 1 tbl.cyb_order_state FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_state, (SELECT TOP 1 tbl.cyb_order_zip FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_zip, (SELECT TOP 1 tbl.cyb_order_phone FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_phone, (SELECT TOP 1 tbl.cyb_order_email FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_email, (SELECT TOP 1 tbl.cyb_order_pay_method FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_pay_method, (SELECT TOP 1 RIGHT(tbl.cyb_order_cc_num, 4) FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_cc_num, (SELECT TOP 1 tbl.cyb_order_cc_expire FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_cc_expire, (SELECT TOP 1 tbl.cyb_order_datetime FROM cyb_order_detail_mast tbl WHERE ((x.cyb_course_code = tbl.cyb_course_code) AND (x.cyb_student_id = tbl.cyb_student_id))) AS cyb_order_datetime FROM cyb_grade_book_mast x INNER JOIN cyb_student_profile_mast y ON x.cyb_student_id = y.cyb_student_id INNER JOIN cyb_required_course_mast z ON x.cyb_student_id = z.cyb_student_id INNER JOIN cyb_course_mast a ON x.cyb_course_code = a.cyb_course_code WHERE ((y.cyb_profile_id_number <> '000000-00') AND ((x.cyb_course_code = 'NAMB001M' OR x.cyb_course_code = 'NAMB001NM') OR (x.cyb_course_code = 'NAMB002M' OR x.cyb_course_code = 'NAMB002NM') OR (x.cyb_course_code = 'NAMB003M' OR x.cyb_course_code = 'NAMB003NM')) AND (x.variable_switch_1 <> 'M') AND (x.cyb_org_id = 11) AND (z.cyb_course_code LIKE x.cyb_course_code) AND (z.cyb_org_id = 11) AND (x.cyb_record_num NOT IN ('99720','99000','98987','99117')) ) ORDER BY x.cyb_course_code ASC OPEN @MyCursor Server: Msg 16950, Level 16, State 2, Line 159 The variable '@MyCursor' does not currently have a cursor allocated to it. Server: Msg 16950, Level 16, State 2, Line 215 The variable '@MyCursor' does not currently have a cursor allocated to it. Server: Msg 16950, Level 16, State 2, Line 218 The variable '@MyCursor' does not currently have a cursor allocated to it. A Data 0 B Data 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
