RE: Stored Proc Question Help Please
You need to write a cursor, it sounds like. Take a look in the books online to get a sense of how it works. M -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
The syntax I have attached actually uses two temp tables and then unions them at the end, but does use a cursor to loop with. Keep in mind this is SQL 6.5, and some of the functionality might be depricated or changed: create table #temp_origin_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) create table #temp_dest_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) Declare @company_id int --Declare @this_date datetime --Declare @that_date datetime --Select @this_date = '05/23/03' --Select @that_date = '05/24/03' Insert into #temp_origin_records (summary_id,service_type,type,company_id,company_name,sco re,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created,es.probation from evaluatio n_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where es.company_id 0 and es.date_created = @this_date AND date_created @that_date and es.status = 'R' --and service_type = 1 Declare this _cursor CURSOR FOR Select company_id from #temp_origin_records Open this_cursor Fetch Next from this_cursor into @company_id While (@@fetch_status = 0) Begin Insert into #temp_dest_records (summary_id,service_type,type,company_id,company _name,score,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created ,es.probation from evaluation_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where summary_id = (select max(summary_id) from evaluation_summary where date_created @this_date and company_i d = @company_id)-- and service_type = 1 Fetch Next from this_cursor into @company_id End close this_cursor deallocate this_cursor select * from #temp_origin_records union all select * from #temp_dest_records order by company_id, summar y_id desc drop table #temp_origin_records drop table #temp_dest_records -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
Although a trigger would be better. Much more efficient in this case. WG -Original Message- From: Haggerty, Mike [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 15:48 To: CF-Talk Subject: RE: Stored Proc Question Help Please You need to write a cursor, it sounds like. Take a look in the books online to get a sense of how it works. M -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
What's the relationship between the two tables? Ade -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 15:29 To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create PROCEDURE dbo.sp_getId
AS
BEGIN
declare @intError int
if object_id('tempdb..#myTempTable') is not null drop #myTemptable
/* Create Table */
create table #myTempTable
(
customerId int,
blah1 int,
blah2 varchar(200
)
declare @cid int
declare cidCursor cursor local static
for
select customerId from customerIdTable
open cidCursor
fetch cidCursor into @cid
while @@fetch_status=0
begin
insert into #myTempTable
select customerId, blah1, blah2
from myOtherTableorSql
where customerId = @cid
fetch next from aidCursor into @cid
end
close cidCursor
deallocate cidCursor
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ô¿ô Tony
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 7:29 AM
To: CF-Talk
Subject: Stored Proc Question Help Please
This is a very simple question.
I need to do this in SQL Server from a job not a CF page.
I have a table where I store customer IDs. I want to pull the individual IDs and loop
each ID out of the table and run it against another query and write that output of the
query to a tmpTable. How do I do this and if you could help it would be most
appreciated.
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
Thanks I will see what I can do with this. I appreciate you time and your help. -Original Message- From: John Stanley [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 9:53 AM To: CF-Talk Subject: RE: Stored Proc Question Help Please The syntax I have attached actually uses two temp tables and then unions them at the end, but does use a cursor to loop with. Keep in mind this is SQL 6.5, and some of the functionality might be depricated or changed: create table #temp_origin_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) create table #temp_dest_records ( summary_id int, service_type int, type char(25), company_id int, company_name char(255), score int, status char(2), status_int int, date_created datetime, probation char(2) ) Declare @company_id int --Declare @this_date datetime --Declare @that_date datetime --Select @this_date = '05/23/03' --Select @that_date = '05/24/03' Insert into #temp_origin_records (summary_id,service_type,type,company_id,company_name,sco re,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created,es.probation from evaluatio n_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where es.company_id 0 and es.date_created = @this_date AND date_created @that_date and es.status = 'R' --and service_type = 1 Declare this _cursor CURSOR FOR Select company_id from #temp_origin_records Open this_cursor Fetch Next from this_cursor into @company_id While (@@fetch_status = 0) Begin Insert into #temp_dest_records (summary_id,service_type,type,company_id,company _name,score,status,status_int,date_created,probation) Select es.summary_id,es.service_type,lt.description,es.company_id,c.name,es.score,e s.status, status_int = case When es.status = 'R' then 1 else0 end, es.date_created ,es.probation from evaluation_summary es join company c on c.id = es.company_id join leg_type lt on lt.id = es.service_type where summary_id = (select max(summary_id) from evaluation_summary where date_created @this_date and company_i d = @company_id)-- and service_type = 1 Fetch Next from this_cursor into @company_id End close this_cursor deallocate this_cursor select * from #temp_origin_records union all select * from #temp_dest_records order by company_id, summar y_id desc drop table #temp_origin_records drop table #temp_dest_records -Original Message- From: Eric Creese [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:29 AM To: CF-Talk Subject: Stored Proc Question Help Please This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Stored Proc Question Help Please
Eric Creese wrote: This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. Can't you just do: INSERT INTO temptable (field [, field]) SELECT field [, field] FROM othertable WHERE id IN (SELECT id FROM customertable) Why do you want to loop? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please
I guess I could try this -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:07 AM To: CF-Talk Subject: Re: Stored Proc Question Help Please Eric Creese wrote: This is a very simple question. I need to do this in SQL Server from a job not a CF page. I have a table where I store customer IDs. I want to pull the individual IDs and loop each ID out of the table and run it against another query and write that output of the query to a tmpTable. How do I do this and if you could help it would be most appreciated. Can't you just do: INSERT INTO temptable (field [, field]) SELECT field [, field] FROM othertable WHERE id IN (SELECT id FROM customertable) Why do you want to loop? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Host with the leader in ColdFusion hosting. Voted #1 ColdFusion host by CF Developers. Offering shared and dedicated hosting options. www.cfxhosting.com/default.cfm?redirect=10481 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Stored Proc Question Help Please-SOLVED
Thanks for everyones input! ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
