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
>
>

Returning random records and NOT similar (random questions)

Hi,

I need to extract randomly 5 records from the table "Questions". Now I use

SELECT TOP 5 FROM Questions ORDERBY NEWID()

And it works. The problem is that I need an additional thing: if SQL
extracts record with ID=4, then it should not extract record with ID=9,
because they are similar. I mean, I'd like something to tell SQL that if it
extracts some questions, then it SHOULD NOT extract other ones.

How can I do it?

Thanks!

Luke"Luke" <nospam@.nospam.com> wrote in message news:<%ejcc.18367$hc5.868453@.news3.tin.it>...
> Hi,
> I need to extract randomly 5 records from the table "Questions". Now I use
> SELECT TOP 5 FROM Questions ORDERBY NEWID()
> And it works. The problem is that I need an additional thing: if SQL
> extracts record with ID=4, then it should not extract record with ID=9,
> because they are similar. I mean, I'd like something to tell SQL that if it
> extracts some questions, then it SHOULD NOT extract other ones.
> How can I do it?
> Thanks!
> Luke

You need to define some logic to say why 4 and 9 are "similar". For
example, should ABS(x-y) > 10 be true for all possible combinations of
numbers in the result set? Or since you're retrieving questions,
perhaps they're in groups, ie. questions 1-20 are on the same topic,
21-40 on a different topic etc., and you want only one random question
from each topic?

Depending on what logic you decide, you might want to consider doing
this in a client application - if the first value you retrieve affects
which ones you can retrieve later, then a cursor-based solution might
be the only way to do it on the server side, and that will be slow. It
may be faster to use a client app which retrieves the maximum and
minimum values (or whatever data you need to reference in your logic),
and then applies your pseudo-random algorithm.

Simon

Returning Identity column in SQL 7

I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7

Anyone remember how they did this ?@.@.Identity