RE: Stored Procedures - Am I going insane?
Philip,
as Paul stated, often in SQL you'll have to resort to using cursors to
perform operations on a recordset on a per row basis. If you can avoid them
do, they really are costly in performance terms
Quote from the Wrox Professional SQL Server 7 programming book by Robert
Viera:
"Cursors are a resource pig and will almost always produce 100 times or
worse negative performance impact [but] Cursors are going to be the answer
anytime a solution must be done on a row-by-row basis."
So that said, heres the basic syntax:
DECLARE [yourcursor] CURSOR
{OPTIONS}
FOR
[your sql statement to grab a recordset]
OPEN [yourcursor]
FETCH NEXT FROM [yourcursor] INTO [your column holding sql variables]
WHILE @@FETCH_STATUS =0
BEGIN
[perform your row by row operation]
FETCH NEXT FROM [yourcursor] INTO [your column holding sql variables]
END
CLOSE [yourcursor]
DEALLOCATE [yourcursor]
so you could do the following as an example
DECLARE @col1 NUMERIC(9), @col2 NUMERIC(9)
DECLARE boris CURSOR
LOCAL
FOR
SELECT thiscolumn, thatcolumn FROM myTable WHERE active = 1
OPEN boris
FETCH NEXT FROM boris INTO @col1, @col2
WHILE @@FETCH_STATUS = 0
BEGIN
/* row by row operations begin here */
IF (@col1 = 0)
BEGIN
UPDATE mySecondTable
SET thiscolumn = @col2
WHERE this_id 10
END
ELSE
BEGIN
DELETE FROM mySecondTable
WHERE this_id = 10
END
FETCH NEXT FROM boris INTO @col1, @col2
END
CLOSE boris
DEALLOCATE boris
Now whilst this specific query could probably be done much easier without
cursors (I'm just making it up as I go along for example), I'm hoping you
get the gist of it
---
Rich Wild
Senior Web Designer
---
e-mango.com ltd Tel: 01202 587 400
Lansdowne Place Fax: 01202 587 401
17 Holdenhurst Road
Bournemouth Mailto:[EMAIL PROTECTED]
BH8 8EW, UK http://www.e-mango.com
---
This message may contain information which is legally
privileged and/or confidential. If you are not the
intended recipient, you are hereby notified that any
unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such
notification notwithstanding, any comments, opinions,
information or conclusions expressed in this message
are those of the originator, not of e-mango.com ltd,
unless otherwise explicitly and independently indicated
by an authorised representative of e-mango.com ltd.
---
-Original Message-
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: 19 February 2001 20:52
To: CF-Talk
Subject: Stored Procedures - Am I going insane?
Just to prove to myself that I'm not going insane...
Is it possible in a SQL Server Stored Procedure to have
dynamic table names?
I'm currently trying to;
Create Procedure update_pages (@tablename varchar(255), @rowlist
varchar(255))
as
begin
update [@tablename] inner join Central_Pages on
[@tablename].ID=Central_Pages.ID
set [@tablename].Title=Central_Pages.Title,
[@tablename].Body=Central_Pages.Body
where [@tablename].ID in (@rowlist)
end
GO
When I check the syntax I get "Error 156: Incorrect syntax
near the keyword
'inner'."
Please tell me I'm not going completely mad and that it'll
let me do this
The problem is that I've got to update upto 200 tables and
only specific
pages, I don't specifically want to do this via CF (for
obvious reasons)
While I'm on the SP subject - something I've never had to do
inside one is
loop over a select and do something on the result... is this possible?
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - Am I going insane?
worse negative performance impact [but] Cursors are going to be the answer anytime a solution must be done on a row-by-row basis." you can often use a series of set operations against temp tables instead of server side cursors or just plain sql if you're joe celko (or maybe tom potts who writes some of the most imaginative sql code). ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - Am I going insane? - Here's what I want to do
OK, here's a full description of what I want to accomplish and some background We are working for a client who does Conferences - each conference has a mini-site which has pages describing it and central "How to get there" type pages There is one core site which contains the general pages, which are copied over when a new mini-site is created There will be about 200 or so active mini-sites Whenever some of the central copy is updated, it should be allowed to be propogated to all active sites, updating pages which already exist and adding pages which don't exist yet If I was to do this in CF it'd take absolutely ages (esp if you're propogating 10 pages to 200 sites, checking the existance of each page to add/update), so I'd rather do it in one Stored Procedure, but I've never used one for anything this demanding/complex Hope this explains more of what I want/need Thanks in advance for any assistance Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - Am I going insane? - Here's what I want to do
Oops, one thing to complicate matters - the primary key on the tables aren't Identities OK, here's a full description of what I want to accomplish and some background We are working for a client who does Conferences - each conference has a mini-site which has pages describing it and central "How to get there" type pages There is one core site which contains the general pages, which are copied over when a new mini-site is created There will be about 200 or so active mini-sites Whenever some of the central copy is updated, it should be allowed to be propogated to all active sites, updating pages which already exist and adding pages which don't exist yet If I was to do this in CF it'd take absolutely ages (esp if you're propogating 10 pages to 200 sites, checking the existance of each page to add/update), so I'd rather do it in one Stored Procedure, but I've never used one for anything this demanding/complex Hope this explains more of what I want/need Thanks in advance for any assistance Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - Am I going insane?
Paul said: sp_executesql will gain you something in terms of optimization, etc. but this isn't the kind of thing that ought to be in an sp (unless you're doing this for security or management reasons) Why not? I've been taught by our database folks that the more stuff we can move into the database, the better - that cf is best used for outputting/formatting but that the processor-intensive stuff (loops, etc) should be moved to the db when possible. What's the rational for saying that you shouldn't? -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - Am I going insane?
I've heard much the same thing from our dba's, but there are many exceptions. I actually have run into a few cases where it was faster to do some of the logic in CF and run the sp inside my CF loops than to do the whole thing in one sp. If you run into enough performance problems you'll end up experimenting with all kinds of combinations of CF and sp's. I hadn't heard about the cursors being "resource pigs". I'll have to ask our crew about that. We haven't resorted to them too much. Have tended to use temporary tables in those cases. -Original Message- From: Deanna L. Schneider [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 20, 2001 8:53 AM To: CF-Talk Subject: Re: Stored Procedures - Am I going insane? Paul said: sp_executesql will gain you something in terms of optimization, etc. but this isn't the kind of thing that ought to be in an sp (unless you're doing this for security or management reasons) Why not? I've been taught by our database folks that the more stuff we can move into the database, the better - that cf is best used for outputting/formatting but that the processor-intensive stuff (loops, etc) should be moved to the db when possible. What's the rational for saying that you shouldn't? -d Deanna Schneider Interactive Media Developer UWEX Cooperative Extension Electronic Publishing Group 103 Extension Bldg 432 N. Lake Street Madison, WI 53706 (608) 265-7923 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - Am I going insane?
- Original Message - From: "Deanna L. Schneider" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Tuesday, February 20, 2001 7:52 AM Subject: Re: Stored Procedures - Am I going insane? Paul said: sp_executesql will gain you something in terms of optimization, etc. but this isn't the kind of thing that ought to be in an sp (unless you're doing this for security or management reasons) Why not? I've been taught by our database folks that the more stuff we can move into the database, the better - that cf is best used for outputting/formatting but that the processor-intensive stuff (loops, etc) should be moved to the db when possible. What's the rational for saying that you shouldn't? -d You want to be careful here. While I agree that moving complex processes out of CF and into the database world is beneficial, its really more important HOW you do it, than WHETHER you do it. By that I mean, SQL is a relational language. Simply taking a giant loop out of CF and putting it into SQL using cursors to achieve your looping, is not necessarily going to gain you much, as SQL was not designed for looping (inherently) like a Cold Fusion type language is. The best results that I have achieved in this area Deanna, have come when working directly with a relational database developer in order to take a cold fusion loop out of the CF world, and develop a non-looping, relational solution, written entirely in SQL. CF is then used, as you mentioned, as an outputting/formatting vehicle. While it is not always practical to attempt this type of a solution to every problem, the increased performance we've seen generally makes it a worth while endeavor. Hope that helps. Brian ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - Am I going insane?
Dave medinets has some stored proc's that use dynamic table names. Props to him ;) Check this out.. it aint the prettiest code in the world but it is possible. http://medinets.onproject.com/ntm/ Jeremy Allen elliptIQ Inc. -Original Message- From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]] Sent: Monday, February 19, 2001 3:52 PM To: CF-Talk Subject: Stored Procedures - Am I going insane? Just to prove to myself that I'm not going insane... Is it possible in a SQL Server Stored Procedure to have dynamic table names? I'm currently trying to; Create Procedure update_pages (@tablename varchar(255), @rowlist varchar(255)) as begin update [@tablename] inner join Central_Pages on [@tablename].ID=Central_Pages.ID set [@tablename].Title=Central_Pages.Title, [@tablename].Body=Central_Pages.Body where [@tablename].ID in (@rowlist) end GO When I check the syntax I get "Error 156: Incorrect syntax near the keyword 'inner'." Please tell me I'm not going completely mad and that it'll let me do this The problem is that I've got to update upto 200 tables and only specific pages, I don't specifically want to do this via CF (for obvious reasons) While I'm on the SP subject - something I've never had to do inside one is loop over a select and do something on the result... is this possible? Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - Am I going insane?
Why not? I've been taught by our database folks that the more stuff we can why not? sp's aren't really meant for dynamc sql. if i had two logic branches in an sp, these would become 2 separate sp. unless security/management were the main issues. move into the database, the better - that cf is best used for outputting/formatting but that the processor-intensive stuff (loops, etc) should be moved to the db when possible. What's the rational for saying that you shouldn't? i didn't say "not in the database", i said "not in an sp". and the loops here were loops building dynamic sql code. cf builds the sql, the backend executes it. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - Am I going insane?
Consider the following situation: A complex query that is broken down to mutiple queries that require at least one temporary table. If these queries are called from a SP then the temporary table(s) exist only for the duration of that SP. The other way of course is to drop the temp table(s) at the end of the mutliple queries. Just a thought. Keith move into the database, the better - that cf is best used for outputting/formatting but that the processor-intensive stuff (loops, etc) should be moved to the db when possible. What's the rational for saying that you shouldn't? i didn't say "not in the database", i said "not in an sp". and the loops here were loops building dynamic sql code. cf builds the sql, the backend executes it. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Stored Procedures - Am I going insane?
Is it possible in a SQL Server Stored Procedure to have dynamic table names? I'm currently trying to; Create Procedure update_pages (@tablename varchar(255), @rowlist varchar(255)) as begin update [@tablename] inner join Central_Pages on [@tablename].ID=Central_Pages.ID set [@tablename].Title=Central_Pages.Title, [@tablename].Body=Central_Pages.Body where [@tablename].ID in (@rowlist) end GO When I check the syntax I get "Error 156: Incorrect syntax near the keyword 'inner'." Please tell me I'm not going completely mad and that it'll let me do this The problem is that I've got to update up to 200 tables and only specific pages, I don't specifically want to do this via CF (for obvious reasons) You can do what you want, but not as simply as this. Typically, when you're building SQL on the fly, you'll have to build a string, then use EXECUTE (in SQL 6.5) or sp_executesql (in SQL 7 or 2000) to execute the string. 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 Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - Am I going insane?
While I'm on the SP subject - something I've never had to do inside one is loop over a select and do something on the result... is this possible? you do this with server-side cursors, which should be avoided if at all possible. what are you trying to do? ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Stored Procedures - Am I going insane?
Typically, when you're building SQL on the fly, you'll have to build a string, then use EXECUTE (in SQL 6.5) or sp_executesql (in SQL 7 or 2000) to execute the string. sp_executesql will gain you something in terms of optimization, etc. but this isn't the kind of thing that ought to be in an sp (unless you're doing this for security or management reasons). i might use cf if nothing was being returned the sql executed quickly enough or use cf to write the sql then run the whole thing on the db box. DTS is another thing you might look at. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
