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:> Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
