September 6, 2006 at 7:26 am
Good morning,
To check that a record exists in a table using a stored procedure, I have always done this:
select COUNT(*) as nCount FROM aTable WHERE nFieldID = @tnID
IMHO, this is not the best way to accomplish this, and wondered if someone had any advice?
TIA,
-Mark
September 6, 2006 at 7:46 am
Maybe it would be a tad faster to use the return parameter and not create a dataset at all but I don't know how much more speed you're gonna get from this... You'll have to test it yourself and see :
CREATE
PROCEDURE dbo.MySP @ID AS INT
AS
SET NOCOUNT ON
DECLARE @Return AS INT
SELECT @Return = CASE WHEN EXISTS (Select * from dbo.MyTable where id = @ID) THEN 1 ELSE 0 END
RETURN @Return
SET NOCOUNT OFF
GO
September 6, 2006 at 8:15 am
This is how I have done it.
If Exists (Select 1 from TableName where ColumnName = @s_ParameterName)
Begin
Select 'Exists'...Keep doing what Exits Part
End
Else
Begin
Select 'Not Exists'..Keep doing Non Exists Part
End
Thanks
Sreejith
September 6, 2006 at 8:21 am
Ya that works great for dataflow... but I was under the impression that he just needed to know the existense and work with the flow in th application... maybe my intuition is off on this ?!?.
September 8, 2006 at 9:13 am
I was looking for just a procedure that returned whether or not a record or records existed for a particular ID.
September 8, 2006 at 9:32 am
For that I'd suggest my version... by maybe you should turn it into a function so it'd be easier to access.
September 8, 2006 at 9:41 am
I was wondering, and I obviously need to do some tests but where you have:
SELECT @Return = CASE WHEN EXISTS (Select * from dbo.MyTable where id = @ID) THEN 1 ELSE 0 END
Would it be faster (on a larger table) to have:
SELECT @Return = CASE WHEN EXISTS (Select ID from dbo.MyTable where id = @ID) THEN 1 ELSE 0 END
or even a SELECT TOP 1 ID FROM Table .... etc ??
September 8, 2006 at 9:53 am
We already had this discussion here and on many other forums. Nobody has been able to prove that using "1","*" or a single column had an impact on performance. The bottom line is that the server doesn't actually extract the data to a set. It simply check for its existense and returns true or false accordingly.
September 8, 2006 at 9:57 am
That's awesome to know - thank you for your help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply