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

http://blogs.msdn.com/b/bartd/archive/2011/03/03/don-t-depend-on-expression-short-circuiting-in-t-sql-not-even-with-case.aspx