An alert...of sorts

I have written a report which, depending upon the data year you want to analyze, runs against different databases. If you want the 1987 data, for example, you pull from the 1987 database, and so on.

It is no problem to change ("update") the files to the correct year and then run the report...when you remember to do it.

Is there a way to put an "alert" or something into the report which reminds the user to "update" the data files before actually running the report? What would the syntax be?

Or better yet, is there a way to automate the "update" files process based on a parameter such as [?Year}?Alternatives :

-Develop a small application in a visual language (Delphi, VisualBasic, FoxPro)

-Copy the report for each year and name them and configure them accordingly (Report1987, Report1988, and so on)

Alternative to dynamic sql?

I have a procedure that take several paramters and depending of what
values is submitted or not, the procedures shall return different
number of rows. But to simplyfy this my example use just one
parameter, for example Idnr.

If this id is submitted then I will return only the posts with this
idnr, but if this is not submitted, I will return all posts in table.
As I can see I have two options
1. IF @.lcIdNr IS NOT NULL
SELECT *
FROM table
WHERE idnr = @.lcIdNr
ELSE
SELECT *
FROM table

2. Use dynamic SQL.

The first example can work with just one parameter but with a couple
of different input paramters this could be difficult, anyway this is
not a good solution. The second example works fine but as I understand
dynamic sql is not good from the optimizing point of view. So, I don't
want to use either of theese options, so I wonder If there i a way to
work around this with for example a case clause?

Regards
JennyMaybe:

SELECT *
FROM TableX
WHERE idnr = @.lcIdNr OR @.lcIdNr IS NULL

This article explains some of the things you should consider before using
Dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

--
David Portas
----
Please reply only to the newsgroup
--|||SELECT *
FROM Foobar
WHERE idnr = COALESCE(@.lcIdNr, idnr);|||[posted and mailed, vnligen svara i nys]

Jenny (jenny@.megasol.se) writes:
> If this id is submitted then I will return only the posts with this
> idnr, but if this is not submitted, I will return all posts in table.
> As I can see I have two options
> 1. IF @.lcIdNr IS NOT NULL
> SELECT *
> FROM table
> WHERE idnr = @.lcIdNr
> ELSE
> SELECT *
> FROM table
> 2. Use dynamic SQL.
> The first example can work with just one parameter but with a couple
> of different input paramters this could be difficult, anyway this is
> not a good solution. The second example works fine but as I understand
> dynamic sql is not good from the optimizing point of view.

Actually in this case it's the opposite. For these kind of queries,
dynamic SQL usually gives you the best combination performance and
maintainability.

For a longer discussion on the topic, see this article on my web site:
http://www.algonet.se/~sommar/dyn-search.html. (This is not the same
that David referred you too.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Alternative to cursor.

Hi all,
I want to get data from table and do some processing depending on
records of table.
Do i need to use cursor. Or is there any other alternative available
for this.
Any suggestion would be truely appreciated.
thanks in advance.
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegro ups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>
That depends on the type of processing you need to do. In general, using
set based solutions rather than procedural (cursor) solutions is faster in
SQL Server.
If you give some specifics of your problem and the desired results etc. we
can be more helpful here.
Check out: http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
|||what specifically do you want to do?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegro ups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>
|||Try to avoid cursors as they in general don't offer the performance that set
based operations do. Try to do a set based operation if possible.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegro ups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>

Alternative to cursor.

Hi all,
I want to get data from table and do some processing depending on
records of table.
Do i need to use cursor. Or is there any other alternative available
for this.
Any suggestion would be truely appreciated.
thanks in advance.<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>
That depends on the type of processing you need to do. In general, using
set based solutions rather than procedural (cursor) solutions is faster in
SQL Server.
If you give some specifics of your problem and the desired results etc. we
can be more helpful here.
Check out: http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell|||what specifically do you want to do?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>|||Try to avoid cursors as they in general don't offer the performance that set
based operations do. Try to do a set based operation if possible.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>

Alternative to cursor.

Hi all,
I want to get data from table and do some processing depending on
records of table.
Do i need to use cursor. Or is there any other alternative available
for this.
Any suggestion would be truely appreciated.
thanks in advance.<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>
That depends on the type of processing you need to do. In general, using
set based solutions rather than procedural (cursor) solutions is faster in
SQL Server.
If you give some specifics of your problem and the desired results etc. we
can be more helpful here.
Check out: http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell|||what specifically do you want to do?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
___________________________________
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>|||Try to avoid cursors as they in general don't offer the performance that set
based operations do. Try to do a set based operation if possible.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<trialproduct2004@.yahoo.com> wrote in message
news:1160140929.983520.124390@.i3g2000cwc.googlegroups.com...
> Hi all,
> I want to get data from table and do some processing depending on
> records of table.
> Do i need to use cursor. Or is there any other alternative available
> for this.
> Any suggestion would be truely appreciated.
> thanks in advance.
>