2007-05-12

T-SQL - Almost Polymorphic Stored Procedures

One of the bigger complaints of Stored Procedures in T-SQL is their inability to attend to many different functional needs at once. It's often standard practice in many organizations to write several similar scripts that only vary in the search criteria--the same types of data will be returned regardless.

Here's one way to surmise this limitation.

We're going to utilize these features built into T-SQL
• IsNull(a, b) - IsNull is a system function built into T-SQL that will return value a, if it's a is not null. If a is null, return b.
• Default Parameter Values - By defaulting all search criteria to null, we should be able to fully utilize IsNull(a, b)

So here's the code...



CREATE PROCEDURE [GetSomeData]
@Field1 INT = NULL
, @Field2 INT = NULL
, @Field3 VARCHAR(10) = NULL
AS
BEGIN

SELECT
[Field1]
, [Field2]
, [Field3]
FROM [TableName]
WHERE [Field1] = IsNull(@Field1, [Field1])
AND [Field2] = IsNull(@Field2, [Field2])
AND [Field3] = IsNull(@Field3, [Field3])

END


Basically, what we have here...

We've typed out our stored procedure in full, with all of our search criteria, but instead of leaving the criteria as Field1 = @Field1, we've opted to use the identity concept of the underlying data. Like this... Field1 = IsNull(@Field1, Field1). Basically, if the consumer doesn't pass it a value for @Field1, this Sproc will return all values where Field1 = Field1, which should be every record.

Finally, how it's used:



-- Returns All Results from SPROC
EXECUTE [GetSomeData]

-- Returns Results WHERE @Field1 is 1
EXECUTE [GetSomeData] @Field1 = 1

-- Returns Results WHERE @Field2 is 2
EXECUTE [GetSomeData] @Field2 = 2

-- Returns Results WHERE @Field3 is 'text'
EXECUTE [GetSomeData] @Field3 = 'text'

-- Returns Results WHERE Field1 is 1 and @Field3 is 'text'
EXECUTE [GetSomeData] @Field1 = 1, @Field3 = 'text'

-- Returns Results WHERE @Field is 2 and @Field3 is 'text'
EXECUTE [GetSomeData] @Field2 = 2, @Field3 = 'text'




Hope the buds your frustrations!

1 comment:

Melih Özsoy said...

Nice. Thanks for good info