RE: Stored Proc Question
In some cases yes but in reality if you are using query caching with cfquery it can be just as fast. For simple selects you will see negligible difference in as far as the parsing is concerned but there is a hell of a lot of other things to consider with using an SP over CFQUERY which will be faster such as use of memory etc. It is not all about how fast you get results back. -Original Message- From: Andy Ousterhout [mailto:[EMAIL PROTECTED] Sent: 21 January 2005 00:13 To: CF-Talk Subject: RE: Stored Proc Question I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) Use 2 separate queries (in 1 or 2 procedures). Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191323 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
Good point. But you can't use query caching without placing in application or session scope and still use cfqueryparam -Original Message- From: Robertson-Ravo, Neil (RX) In some cases yes but in reality if you are using query caching with cfquery it can be just as fast. For simple selects you will see negligible difference in as far as the parsing is concerned but there is a hell of a lot of other things to consider with using an SP over CFQUERY which will be faster such as use of memory etc. It is not all about how fast you get results back. -Original Message- From: Andy Ousterhout I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191341 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
Caching with CFQUERY? To be honest I rarely use CFQUERY now apart from QoQ so I am a tad rusty on its ins and outs ;-) -Original Message- From: Andy Ousterhout [mailto:[EMAIL PROTECTED] Sent: 21 January 2005 13:51 To: CF-Talk Subject: RE: Stored Proc Question Good point. But you can't use query caching without placing in application or session scope and still use cfqueryparam -Original Message- From: Robertson-Ravo, Neil (RX) In some cases yes but in reality if you are using query caching with cfquery it can be just as fast. For simple selects you will see negligible difference in as far as the parsing is concerned but there is a hell of a lot of other things to consider with using an SP over CFQUERY which will be faster such as use of memory etc. It is not all about how fast you get results back. -Original Message- From: Andy Ousterhout I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191342 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Andy Ousterhout wrote: I thought that even with simple queries that Stored Procs where much faster? That is not my experience. YMMV Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191345 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Has anyone tested simple cached queries with CFQUERY against the performance of sp_executesql and SQL statement strings? Functionally it's exactly the same as cfquery in that variables are replaced with values... however, the DB does the work and the CF server simply issues directives and receives results. The gotcha here is that it's (afaik) a SQL Server only solution... and that sux. Well, at least it does if it's any better. This is really interesting, because on my last major I completely converted to stored procs and views because I had some conditional datasets (i.e. is this user a staffmember or a client? find out, return the right data) that I was able to accomplish with an sproc whereas I'd have had to have CF issue at least 2, possibly 3-5 cfquery routines. Also, with sprocs and views I was able to completely abstract the application from the specifics of the DB schema... which I found to be very cool. I've always heard CF is an application server, compartmentalize functionality and let the DB do what it does best and let CF handle the smallest amount of data it needs to do the job. Has this changed, or is it just much less concrete than I've been thinking of it in the past? On Fri, 21 Jan 2005 15:42:15 +0100, Jochem van Dieten [EMAIL PROTECTED] wrote: Andy Ousterhout wrote: I thought that even with simple queries that Stored Procs where much faster? That is not my experience. YMMV Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191358 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Stored Proc Question
Lets say that I need to look up an invoice by 2 different mechanisms: Internal reference/Key 2 strings Will the execution plan for a stored proc for the Key be different enough from one for the strings to justify creating 2 stored procs? Right now I've got a single stored proc that does both. Andy ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191286 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Andy Ousterhout wrote: Lets say that I need to look up an invoice by 2 different mechanisms: Internal reference/Key 2 strings Will the execution plan for a stored proc for the Key be different enough from one for the strings to justify creating 2 stored procs? Right now I've got a single stored proc that does both. The execution plans will be different enough to justify creating 2 execution plans. This requires 2 queries, but depending on your database you might be able to put multiple queries in one stored procedure. Jochem ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191294 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? create proc spreadInvoice @InvoiceNumber int, /*Provide either Invoice Number or both */ @PeachTreeInvoice char(21), /* Peachtree Invoice Number and */ @PeachtreeKey char(21) /* Peachtree Key */ as /* Procedure to read Customer information by either CustomerKey or PeachtreeKey */ SELECT FROM tabInvoicedItems LEFT OUTER JOIN tabOrderedItems ON tabInvoicedItems.Line = tabOrderedItems.Line AND tabInvoicedItems.OrderNumber = tabOrderedItems.OrderNumber LEFT OUTER JOIN tabItems ON tabInvoicedItems.Item_FK = tabItems.Item_PK LEFT OUTER JOIN tabItemTypes ON tabItems.ItemType_FK = tabItemTypes.ItemType_PK LEFT OUTER JOIN tabCustomers INNER JOIN tabOrders ON tabCustomers.Customer_PK = tabOrders.Customer_FK ON tabOrderedItems.OrderNumber = tabOrders.OrderNumber RIGHT OUTER JOIN tabPeople tabPeople_1 RIGHT OUTER JOIN tabInvoices LEFT OUTER JOIN tabPeople ON tabInvoices.EnteredBy_FK = tabPeople.Person_PK ON tabPeople_1.Person_PK = tabInvoices.ChangedBy_FK ON tabInvoicedItems.InvoiceNumber = tabInvoices.InvoiceNumber WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) ORDER BYtabInvoices.InvoiceNumber, tabInvoicedItems.Line; GO -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Lets say that I need to look up an invoice by 2 different mechanisms: Internal reference/Key 2 strings Will the execution plan for a stored proc for the Key be different enough from one for the strings to justify creating 2 stored procs? Right now I've got a single stored proc that does both. The execution plans will be different enough to justify creating 2 execution plans. This requires 2 queries, but depending on your database you might be able to put multiple queries in one stored procedure. Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191303 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Stored Proc Question
Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) Use 2 separate queries (in 1 or 2 procedures). Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. Jochem ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191307 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Stored Proc Question
I thought that even with simple queries that Stored Procs where much faster? I tried this on a couple of other SELECTS and got noticable improvement. By the way, I always use CFQUERRPARAM. Andy -Original Message- From: Jochem van Dieten Andy Ousterhout wrote: Here is the proc that I am using. You suggest either 2 separate queries or 2 procs? WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber [EMAIL PROTECTED])) OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS NOT NULL) AND (tabInvoices.PeachtreeInvoiceNumber= @PeachtreeInvoice) AND (tabCustomers.Peachtree_FK= @PeachtreeKey)) Use 2 separate queries (in 1 or 2 procedures). Why are you using a stored procedure? This appears to be a simple SELECT that would be equally good with cfqueryparam. If there are no urgent pattern / encapsulation reasons I would probably just use 2 queries in the CF code. Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191309 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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
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
another stored proc question
Hi again, OK, in our current application I,m turning all our queries to stored procs, and its starting to feel like maybe I've bitten off too much, as my knowledge of t-sql is one week, I've ordered a book by Garth Wells, 'T-SQL programming with Stored Procedures', waiting for it to arrive. OK so this what I am trying to do, CFSTOREDPROC PROCEDURE=p_checkMV DATASOURCE=#REQUEST.dsn# USERNAME=#REQUEST.user# PASSWORD=#REQUEST.key# CFPROCRESULT NAME=q_checkMV CFPROCPARAM DBVARNAME=@customerID VALUE=#session.customerid# CFSQLTYPE=CF_SQL_INTEGER TYPE=IN CFPROCPARAM DBVARNAME=@cfID VALUE=#client.cfid# CFSQLTYPE=CF_SQL_CHAR TYPE=IN CFPROCPARAM DBVARNAME=@cftoken VALUE=#client.cftoken# CFSQLTYPE=CF_SQL_CHAR TYPE=IN CFPROCPARAM DBVARNAME=@vehicleList VALUE=#ATTRIBUTES.selectedItems# CFSQLTYPE=CF_SQL_VARCHAR TYPE=IN /CFSTOREDPROC emphasis on CFPROCPARAM DBVARNAME=@vehicleList VALUE=#ATTRIBUTES.selectedItems# CFSQLTYPE=CF_SQL_VARCHAR TYPE=IN which has a value of, 49,X504UTU,X504UTU|50,H7WPT,H7WPT|48,J8WPT,J8WPT|51,ECZ6771,ECZ6771|83,S163T JA,S163TJA now in normal sql this is what I WAS doing, CFQUERY ... DELETE FROMDBO.vehicleSelection WHERE customerid=#session.customerid# AND cfid=#client.cfid# AND cftoken=#client.cftoken# /CFQUERY CFQUERY ... CFLOOP FROM=1 TO=#listlen(ATTRIBUTES.selectedItems, '|')# INDEX=i CFIF NOT listWithinList IS CFSET listWithinList= /CFIF CFSET listWithinList=LISTGETAT(ATTRIBUTES.selectedItems, i, |) INSERT INTO dbo.vehicleSelection (customerID,vehicleID,callsign,registration,cfid,cftoken) VALUES (#session.customerID#, #LISTGETAT(listWithinList, 1, ,)#, '#LISTGETAT(listWithinList, 2, ,)#', '#LISTGETAT(listWithinList, 3, ,)#', #CLIENT.cfid#, #CLIENT.cftoken# /CFLOOP /CFQUERY So in my proc CREATE PROCEDURE p_checkMV ( @customerID int, @cfid int, @cftoken int, @vehicleList VARCHAR ) AS DELETE FROMvehicleSelection WHERE customerid=@customerid AND cfid=@cfid AND cftoken=@cftoken what I do next that would replicate my second cfquery I do not have the foggiest, I'm not sure about what conditional operators I need to use, and what functions are avialable to me to manipulate @vehicleList, Thanks for any help on this. jmc .. __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Stored Proc Question
Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Question
Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. You should be able to use @@ROWCOUNT for this. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Proc Question
If your running a SELECT statement you can include COUNT(*) as an extra column. If you are running another SQL statement which does not return a recordset (INSERT, DELETE, UPDATE) you can check the @@ROWCOUNT something like: IF @@ROWCOUNT = 0 YOUR SECOND QUERY Cheers, Bill In a message dated 8/13/01 12:51:21 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Question
First Query here IF @@rowcount = 0 BEGIN -- Second query here END -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: John Barleycorn [mailto:[EMAIL PROTECTED]] Sent: 13 August 2001 17:49 To: CF-Talk Subject: Stored Proc Question Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Proc Question
@@Rowcount is overwritten as soon as you execute the next query. So, it might be wise to do something like the following: declare @MyRowCount int 'Your first query here' set @MyRowCount = @@RowCount if @MyRowCount = 0 begin 'Your second query here' end This is especially useful if you need to refer to the rowcount in more than one place. My $.25 worth... Shawn Grover -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Monday, August 13, 2001 10:56 AM To: CF-Talk Subject: RE: Stored Proc Question First Query here IF @@rowcount = 0 BEGIN -- Second query here END -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: John Barleycorn [mailto:[EMAIL PROTECTED]] Sent: 13 August 2001 17:49 To: CF-Talk Subject: Stored Proc Question Hello, i'm running a stored proc that contains two queries. if the first query runs and does not return records, i need to run the second query. Can anyone tell me the equivilent of recordCount in Transact-SQL? Thanks. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL Trigger / Stored Proc question
CREATE TRIGGER GetMax_ID ON mytable FOR INSERT AS SELECT mytable_ID FROM INSERTED How would I reference the result within a stored procedure to insert the "mytable_id" in the next query? Neil I don't think you can pass a value out from a trigger - once the trigger is created it exists independently of the rest of your code. But you could put all the logic inside the trigger, or have the trigger call a procedure with all the logic inside it. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: SQL Trigger / Stored Proc question
I don't think you can pass a value out from a trigger - once the trigger sure you can. you can reference its value in queryName.mytable_ID -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Trigger / Stored Proc question
I don't think you can pass a value out from a trigger - once the trigger sure you can. you can reference its value in queryName.mytable_ID But what is the query that queryName refers to? If you say CFQUERY... CREATE TRIGGER...AS SELECT mytable_ID.../CFQUERY then what is executed is the CREATE TRIGGER statement, i.e. it stores the trigger in the database. The trigger only *fires* when a row is inserted. Are you saying that CF is clever enough to pick up the fact that the trigger has fired grab the value from somewhere? The SQL Server documentation says: QUOTE Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. /QUOTE ... QUOTE To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. /QUOUTE I think encapsulating it all in a stored proc would be the best thing. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: SQL Trigger / Stored Proc question
lets nip this myth in the bud But what is the query that queryName refers to? the cfquery for the original insert. Are you saying that CF is clever enough to pick up the fact that the trigger has fired grab the value from somewhere? no, but ODBC sql server are... The SQL Server documentation says: since we're quoting (BoL): "When a trigger fires, results are returned to the calling application, just as with stored procedures." QUOTE Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. /QUOTE where does this bit come from? BoL only *suggests* you refrain from returning results w/triggers because it would require all applications accessing that table be able to handle the returned results. QUOTE To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that you've taken this out of context. see the 1st line for this "quote" above. triggers do have their place in development toolboxes. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Trigger / Stored Proc question
Paul, triggers do have their place in development toolboxes. Sure, I never said otherwise. Triggers are great. I've built systems that rely completely on them. But what is the query that queryName refers to? the cfquery for the original insert. Are you saying that CF is clever enough to pick up the fact that the trigger has fired grab the value from somewhere? no, but ODBC sql server are... So let me get this straight, if you have a trigger in your database like this: CREATE TRIGGER table1_insert ON table1 FOR INSERT AS SELECT table1_ID FROM INSERTED /CFQUERY and have a .CFM containing this: CFQUERY name="insertRow"... INSERT into table1 (col2) VALUES (#value2#) /CFQUERY then you can refer to #insertRow.table1_ID# and it will contain the ID that was inserted for you by SQL Server, despite no such variable/column being mentioned in the CFQUERY, because the SQL Server ODBC driver returns the trigger variable in response to the INSERT statement and CF puts that in the CFQUERY results accordingly? If so... cool! Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Trigger / Stored Proc question
quite righta trigger cannot contain a select and therefore cannot return a resultset or return a parameter -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: 25 August 2000 11:38 To: '[EMAIL PROTECTED]' Subject: RE: SQL Trigger / Stored Proc question I don't think you can pass a value out from a trigger - once the trigger sure you can. you can reference its value in queryName.mytable_ID But what is the query that queryName refers to? If you say CFQUERY... CREATE TRIGGER...AS SELECT mytable_ID.../CFQUERY then what is executed is the CREATE TRIGGER statement, i.e. it stores the trigger in the database. The trigger only *fires* when a row is inserted. Are you saying that CF is clever enough to pick up the fact that the trigger has fired grab the value from somewhere? The SQL Server documentation says: QUOTE Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. /QUOTE ... QUOTE To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. /QUOUTE I think encapsulating it all in a stored proc would be the best thing. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL Trigger / Stored Proc question
Sorry, my friend, but you are wrong here. Triggers can contain hundreds of lines of complicated code to enforce complex user-defined business rules, and they can contain SELECT statements and also return result sets (in T/SQL)-- which can be configured to look like parameters if the result set is a single row. I lectured on this at CFUN2K last month, and our upcoming high-end ecommerce product uses these techniques extensively to create a very robust and scalable app. The meat of the issue is this: inserting a table row from a stored procedure, then gaining access to the Indentity column value just inserted. The trigger Neil shows will indeed produce a result that can be accessed in CFML after CFQUERY is run by using QueryName.ColumnName. The problem is: how do you access this from a stored procedure that inserts the row? The answer is this: reference @@IDENTITY in the stored procedure IMMEDIATELY after the statement that inserts the table row. This won't make use of the trigger's SELECT result, but it will give you the value of the Identity column most recently inserted into a table. BTW, David and I will be teaching this and many other techniques during next week's Ecommerce Development with ColdFusion seminar in Memphis. If you want a half-price pass, then just email a request to [EMAIL PROTECTED], but hurry because today is the final day of registration. Hope this helps. Cheers! :) At 01:42 PM 8/25/00 +0100, you wrote: quite righta trigger cannot contain a select and therefore cannot return a resultset or return a parameter -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: 25 August 2000 11:38 To: '[EMAIL PROTECTED]' Subject: RE: SQL Trigger / Stored Proc question I don't think you can pass a value out from a trigger - once the trigger sure you can. you can reference its value in queryName.mytable_ID But what is the query that queryName refers to? If you say CFQUERY... CREATE TRIGGER...AS SELECT mytable_ID.../CFQUERY then what is executed is the CREATE TRIGGER statement, i.e. it stores the trigger in the database. The trigger only *fires* when a row is inserted. Are you saying that CF is clever enough to pick up the fact that the trigger has fired grab the value from somewhere? The SQL Server documentation says: QUOTE Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. /QUOTE ... QUOTE To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. /QUOUTE I think encapsulating it all in a stored proc would be the best thing. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Respectfully, Adam Phillip Churvis President Productivity Enhancement, Inc. * PRODUCTIVITY ENHANCEMENT, INC. * * * *Publishers of the CommerceBlocks line of modular development tools* * for ColdFusion * * * * Website: http://www.commerceblocks.com E-mail: [EMAIL PROTECTED] * * Phone: 770-446-8866Fax:770-242-0410
RE: SQL Trigger / Stored Proc question
I stand corrected!...thanks. Out of curiosity - When faced with this sort of thing I always reference the INSERTED table to get the ID rather than @@IDENTITY. I know it will be marginal if anything but will @@IDENTITY be quicker? are there any other advantages or is it essentially the same thing? -Original Message- From: Adam Phillip Churvis [mailto:[EMAIL PROTECTED]] Sent: 25 August 2000 14:33 To: [EMAIL PROTECTED] Subject: RE: SQL Trigger / Stored Proc question Sorry, my friend, but you are wrong here. Triggers can contain hundreds of lines of complicated code to enforce complex user-defined business rules, and they can contain SELECT statements and also return result sets (in T/SQL)-- which can be configured to look like parameters if the result set is a single row. I lectured on this at CFUN2K last month, and our upcoming high-end ecommerce product uses these techniques extensively to create a very robust and scalable app. The meat of the issue is this: inserting a table row from a stored procedure, then gaining access to the Indentity column value just inserted. The trigger Neil shows will indeed produce a result that can be accessed in CFML after CFQUERY is run by using QueryName.ColumnName. The problem is: how do you access this from a stored procedure that inserts the row? The answer is this: reference @@IDENTITY in the stored procedure IMMEDIATELY after the statement that inserts the table row. This won't make use of the trigger's SELECT result, but it will give you the value of the Identity column most recently inserted into a table. BTW, David and I will be teaching this and many other techniques during next week's Ecommerce Development with ColdFusion seminar in Memphis. If you want a half-price pass, then just email a request to [EMAIL PROTECTED], but hurry because today is the final day of registration. Hope this helps. Cheers! :) At 01:42 PM 8/25/00 +0100, you wrote: quite righta trigger cannot contain a select and therefore cannot return a resultset or return a parameter -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: 25 August 2000 11:38 To: '[EMAIL PROTECTED]' Subject: RE: SQL Trigger / Stored Proc question I don't think you can pass a value out from a trigger - once the trigger sure you can. you can reference its value in queryName.mytable_ID But what is the query that queryName refers to? If you say CFQUERY... CREATE TRIGGER...AS SELECT mytable_ID.../CFQUERY then what is executed is the CREATE TRIGGER statement, i.e. it stores the trigger in the database. The trigger only *fires* when a row is inserted. Are you saying that CF is clever enough to pick up the fact that the trigger has fired grab the value from somewhere? The SQL Server documentation says: QUOTE Triggers can include any number and kind of Transact-SQL statements except SELECT. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. /QUOTE ... QUOTE To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. /QUOUTE I think encapsulating it all in a stored proc would be the best thing. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** --- - -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. --- --- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Respectfully, Adam Phillip Churvis President Productivity Enhancement, Inc. * PRODUCTIVITY ENHANCEMENT, INC
RE: SQL Trigger / Stored Proc question
I always like to use explicit references if possible-- @@IDENTITY is used here because it is the only way to do this. "Faster" in terms of time difference between these two techniques should not even be a consideration because, for all intents and purposes, it is negligible, and there are countless other better opportunities in any app to pick up speed. Hope this helps. Well, it's time for me to feed my trusty horse, Gefilte, so I simply must go... At 02:39 PM 8/25/00 +0100, you wrote: I stand corrected!...thanks. Out of curiosity - When faced with this sort of thing I always reference the INSERTED table to get the ID rather than @@IDENTITY. I know it will be marginal if anything but will @@IDENTITY be quicker? are there any other advantages or is it essentially the same thing? Respectfully, Adam Phillip Churvis President Productivity Enhancement, Inc. * PRODUCTIVITY ENHANCEMENT, INC. * * * *Publishers of the CommerceBlocks line of modular development tools* * for ColdFusion * * * * Website: http://www.commerceblocks.com E-mail: [EMAIL PROTECTED] * * Phone: 770-446-8866Fax:770-242-0410* * * -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: SQL Trigger / Stored Proc question
then you can refer to #insertRow.table1_ID# and it will contain the ID that was inserted for you by SQL Server, despite no such variable/column being mentioned in the CFQUERY, because the SQL Server ODBC driver returns the yes. that value is created by sql server passed back to whatever app called it. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
SQL Trigger / Stored Proc question
Lets say I use the standard insert trigger like so: CREATE TRIGGER GetMax_ID ON mytable FOR INSERT AS SELECT mytable_ID FROM INSERTED How would I reference the result within a stored procedure to insert the "mytable_id" in the next query? Thanks, Neil p.s. Maybe someone knows why CFTree's folder image doesn't work on one of my server?! Thanks. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
SQL Stored Proc Question
Hi, Just wondering if anyone can help with a SQL Server 7 Stored Procedure question. I have a stored procedure that returns a group of fields from a table. What I'd like to do is return a field which is the result of another stored procedure that does something to one of the fields from the table For Example Select TheId, TheDate, NewConvertedDate = (Execute storedprocedure TheTable.TheDate) FROM TheTable I know that I can nest stored procedures in other stored procs, however, I can't seem to find the right syntax (if it's possible) to return the result of a stored procedure as another field in the same select. Is this possible? Can anyone help with the syntax? Thanks Nick -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
