Set RowCount Alternatives [solved]
-
Hi guys, Here's what I'm trying to accomplish. better said I'm trying to determine the speediest way of doing it. I need to check if lets say orders exists for a customer and then do some more stuff. so far the speediest way seems to be something like this:
use [db]
go
Set RowCount 1/*stop after the first row that matches*/
go
Select ID from Table where ColName='Value'
go
Set RowCount 0/*set rowcount back to 0 for other queries*/basically I want the sql server to stop processing as soon as a match is found. As you know, TOP doesn't work that way. Something like this in C# :
foreach(var item in colection){
if(conditon) return true;
}I'm no SQL expert. So, sorry if the question seems silly for some of you DB admin guys. So the question is this: Is there a faster/better way of achieving this? [add] Never mind. Exists() :doh: So caught up in the idea that I totally forgot about it. [/add]
All the best, Dan
modified on Thursday, June 9, 2011 5:08 AM
-
Hi guys, Here's what I'm trying to accomplish. better said I'm trying to determine the speediest way of doing it. I need to check if lets say orders exists for a customer and then do some more stuff. so far the speediest way seems to be something like this:
use [db]
go
Set RowCount 1/*stop after the first row that matches*/
go
Select ID from Table where ColName='Value'
go
Set RowCount 0/*set rowcount back to 0 for other queries*/basically I want the sql server to stop processing as soon as a match is found. As you know, TOP doesn't work that way. Something like this in C# :
foreach(var item in colection){
if(conditon) return true;
}I'm no SQL expert. So, sorry if the question seems silly for some of you DB admin guys. So the question is this: Is there a faster/better way of achieving this? [add] Never mind. Exists() :doh: So caught up in the idea that I totally forgot about it. [/add]
All the best, Dan
modified on Thursday, June 9, 2011 5:08 AM
How about
select c.customer_id
from customer_table c
where exists ( select rowid from orders_table t
where t.customer_id = c.customer_id
)Should give you a list of customer ids that have orders. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
How about
select c.customer_id
from customer_table c
where exists ( select rowid from orders_table t
where t.customer_id = c.customer_id
)Should give you a list of customer ids that have orders. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Yeah thanks. I figured it out. I just wanted the fastest way of seeing if something exists() :doh: and then go do the job. [add] This is what I wanted:
IF Exists(Select ID from Table1 where ColName='Value')
Insert Into...I'm laughing my ass out now after the 30 min feverish thinking(about speed) and searching. It was right in from of me. Doh [/add] Thanks anyway. :thumbsup:
All the best, Dan
modified on Wednesday, June 8, 2011 2:44 PM
-
Yeah thanks. I figured it out. I just wanted the fastest way of seeing if something exists() :doh: and then go do the job. [add] This is what I wanted:
IF Exists(Select ID from Table1 where ColName='Value')
Insert Into...I'm laughing my ass out now after the 30 min feverish thinking(about speed) and searching. It was right in from of me. Doh [/add] Thanks anyway. :thumbsup:
All the best, Dan
modified on Wednesday, June 8, 2011 2:44 PM
MDL=>Moshu wrote:
seeing if something exists() :doh: and then go do the job
If you're going to do it, just do it, don't check first.
-
MDL=>Moshu wrote:
seeing if something exists() :doh: and then go do the job
If you're going to do it, just do it, don't check first.
:thumbsup: Generally it's a very good idea. But the thing is this. I have a production app(scanners and all that) and normally a interface for setting up the lines,projects... I did not allow them to delete a PN. Now they want that feature. But in order to not delete any production data I need to check if any production data exists for that PN and if not, then and only then delete all stuff related to that PN. Else refuse it. Yeah I know SQL would return an error complaining about FK and stuff but I don't want the user to see that. So first check and if not exists then delete. Else show a message to the user that production datas exists for that PN and delete has been aborted. :)
All the best, Dan
modified on Thursday, June 9, 2011 5:05 AM