Re-usable WHERE clauses with UDFs?

Hi again,
I have 10's of SPs using a very similar WHERE clause and I wonder if it is
possible to write UDF fullfilling the same task.
The table name is different most of the time but the fieldname is always the
same. The table name must be passed to the UDF.
-- current form --
SELECT b.intUserID
FROM tblUserSavedSearch b
WHERE
(
-- this month
month(b.dtDateEntered) = case @.range WHEN 1 THEN month(getdate()) ELSE
month(b.dtDateEntered) END and
year(b.dtDateEntered) = case @.range WHEN 1 THEN year(getdate()) ELSE
year(b.dtDateEntered) END and
-- last month
month(b.dtDateEntered) = case @.range WHEN 2 THEN month(getdate()) - 1 ELSE
month(b.dtDateEntered) END and
year(b.dtDateEntered) = case @.range WHEN 2 THEN year(getdate()) ELSE
year(b.dtDateEntered) END and
-- today
day(b.dtDateEntered) = case @.range WHEN 3 THEN day(getdate()) ELSE
day(b.dtDateEntered) END and
month(b.dtDateEntered) = case @.range WHEN 3 THEN month(getdate()) ELSE
month(b.dtDateEntered) END and
year(b.dtDateEntered) = case @.range WHEN 3 THEN year(getdate()) ELSE
year(b.dtDateEntered) END and
-- this year
year(b.dtDateEntered) = case @.range WHEN 4 THEN year(getdate()) ELSE
year(b.dtDateEntered) END and
-- last year
year(b.dtDateEntered) = case @.range WHEN 5 THEN year(getdate()) - 1 ELSE
year(b.dtDateEntered) END
)
I was looking for a solution something like
SELECT b.intUserID
FROM tblUserSavedSearch b
WHERE b.dtDateEntered IN (SELECT dbo.fx_dateRange(@.range, @.tablename))
Is it possible, above is just a simplyfied example. The real date range is
far more complex and I hate to go through 10's of SP everytime there is a
small change in the various ranges that are in use.
Thanks in advance for any insights
ChristianI think a calendar table will help you:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:eN432X4nFHA.2536@.TK2MSFTNGP10.phx.gbl...
> Hi again,
> I have 10's of SPs using a very similar WHERE clause and I wonder if it is
> possible to write UDF fullfilling the same task.
> The table name is different most of the time but the fieldname is always
the
> same. The table name must be passed to the UDF.
> -- current form --
> SELECT b.intUserID
> FROM tblUserSavedSearch b
> WHERE
> (
> -- this month
> month(b.dtDateEntered) = case @.range WHEN 1 THEN month(getdate()) ELSE
> month(b.dtDateEntered) END and
> year(b.dtDateEntered) = case @.range WHEN 1 THEN year(getdate()) ELSE
> year(b.dtDateEntered) END and
> -- last month
> month(b.dtDateEntered) = case @.range WHEN 2 THEN month(getdate()) - 1 ELSE
> month(b.dtDateEntered) END and
> year(b.dtDateEntered) = case @.range WHEN 2 THEN year(getdate()) ELSE
> year(b.dtDateEntered) END and
> -- today
> day(b.dtDateEntered) = case @.range WHEN 3 THEN day(getdate()) ELSE
> day(b.dtDateEntered) END and
> month(b.dtDateEntered) = case @.range WHEN 3 THEN month(getdate()) ELSE
> month(b.dtDateEntered) END and
> year(b.dtDateEntered) = case @.range WHEN 3 THEN year(getdate()) ELSE
> year(b.dtDateEntered) END and
> -- this year
> year(b.dtDateEntered) = case @.range WHEN 4 THEN year(getdate()) ELSE
> year(b.dtDateEntered) END and
> -- last year
> year(b.dtDateEntered) = case @.range WHEN 5 THEN year(getdate()) - 1 ELSE
> year(b.dtDateEntered) END
> )
> I was looking for a solution something like
> SELECT b.intUserID
> FROM tblUserSavedSearch b
> WHERE b.dtDateEntered IN (SELECT dbo.fx_dateRange(@.range, @.tablename))
> Is it possible, above is just a simplyfied example. The real date range is
> far more complex and I hate to go through 10's of SP everytime there is a
> small change in the various ranges that are in use.
> Thanks in advance for any insights
> Christian
>
>