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:>
Get the mailserver that powers this list at
http://www.coolfusion.com
