How can I make this SQL Bit scalar function more efficient?

Question

I'd like to make this SQL scalar function more efficient. How can I do that?

CREATE FUNCTION [dbo].[fnFilterBySampleType]
(
 @context VARCHAR(10),
 @contextId INT,
 @sampleTypeId INT
)
RETURNS BIT
AS
BEGIN
 IF (
 @context = 'batch' AND @sampleTypeId = 247
 AND EXISTS(
 SELECT * 
 FROM batch 
 WHERE batchid = @contextId 
 AND (batch.sampletypeid = @sampleTypeId OR batch.sampletypeid IS NULL)
 ) 
 ) RETURN 1 
 IF (
 @context = 'batch' AND @sampleTypeId = 3301
 AND EXISTS(
 SELECT * 
 FROM batch 
 WHERE batchid = @contextId 
 AND batch.sampletypeid = @sampleTypeId
 ) 
 ) RETURN 1 
 IF (
 @context = 'batch' AND @sampleTypeId IS NULL
 AND EXISTS(
 SELECT * 
 FROM batch 
 WHERE batchid = @contextId 
 AND batch.sampletypeid IS NULL
 ) 
 ) RETURN 1 
 IF (
 @context = 'sample' AND @sampleTypeId = 247
 AND EXISTS(
 SELECT * 
 FROM sample
 WHERE sampleid = @contextId 
 AND ([sample].sampletypeid = @sampleTypeId OR [sample].sampletypeid IS NULL)
 ) 
 ) RETURN 1 
 IF (
 @context = 'sample' AND @sampleTypeId = 3301
 AND EXISTS(
 SELECT * 
 FROM sample
 WHERE sampleid = @contextId 
 AND sample.sampletypeid = @sampleTypeId
 ) 
 ) RETURN 1 
 IF (
 @context = 'sample' AND @sampleTypeId IS NULL
 AND EXISTS(
 SELECT * 
 FROM sample
 WHERE sampleid = @contextId 
 AND [sample].sampletypeid IS NULL
 ) 
 ) RETURN 1 
 RETURN 0
END

Answer 1

I am rewriting my answer to be more clear...

the way you have it written the exists clause will be executed many times...

You should move the exists clauses inside the if block so that in the cases that the other parts don't match it will be forced to short circuit. You can't depend on SQL server tsql statements to short circuit in the manner you expect.

So this will not execute the inner "IF exists" if the @context isn't 'batch' etc...

The way you have it written it may or may not execute the exists query.

IF (@context = 'batch' AND @sampleTypeId = 247)
begin
 if EXISTS(
 SELECT * 
 FROM batch 
 WHERE batchid = @contextId 
 AND (batch.sampletypeid = @sampleTypeId OR batch.sampletypeid IS NULL)
 ) RETURN 1 ELSE RETURN 0
end

for more reading on this subject check out this articles.

http://beingmarkcohen.com/?p=62

http://www.sqlmag.com/article/tsql3/short-circuit

Link

• JustBeingHelpful: sorry, I accidentally gave you an upvote. This is not correct though. Adding more tags so others will see it.
• JustBeingHelpful: where did the 3301 and NULL checks go?
• John Sobolewski: I didn't rewrite them all just the first one.
• JustBeingHelpful: oh ok. is there a way I can combine more than 1 of them above to a single query?
• John Sobolewski: there might be but I think doing so would make the code more confusing.