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:>
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
