Wednesday, November 29, 2006

Survival series - Sql Server, Stored Procedures with non mandatory parameters

With this post I begin the "Survival Series", i.e. some post with answers to little recurring problems that recurringly make many people waste a lot of times "reinventing the wheel".
Really little things, but useful, that I encounter in my programming life :-).

Let's start with this: How can I have a stored procedure with sime parameters that are actually "non-mandatory" ?
Well, this is really easy... but often I see people crash their heads on this "easy" subjects.
Let's see an example: I have the need to call a "search" stored procedure, that accepts 3 parameters (2 varchars and 1 integer) and based on these 3 values must query some tables and return the corresponding resultset; but from the calling app I can have only one, or two, or three values on these parameters.
What I write is (say we have a "Pubs" table):

CREATE PROCEDURE dbo.SearchPub
(
@txtCriteria VARCHAR(30),
@txtYear VARCHAR(4),
@idArgument INT
)
AS

SELECT *
FROM Pubs
where 1=1
AND (@txtCriteria IS NULL OR Content like '%' + @txtCriteria + '%')
AND (@txtYear IS NULL OR PubYear = @txtYear)
AND (@idArgument IS NULL OR Argument = @idArgument)
ORDER BY PubYear desc

GO


As you can see in the Where part, with the three "OR" I can easily manage calls like:

EXEC dbo.SearchPub NULL, NULL, NULL
EXEC dbo.SearchPub NULL, '2006', NULL
EXEC dbo.SearchPub "searching for this", NULL, 3


Hope this can be useful... Bye

No comments: