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