"Christian Perthen" <abracadab***@dontreplytothisaddress.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
>
>
>
>