I'm not going to review your code for correctness, but you missed the point of the article.  The article is about avoiding dynamic SQL by using Case.  I'm not saying...

Also, since you restored master from your NT system, it has the information about your databases in tables like sysdatabases.   At this point, if you don't want to see those db's...

Take a look at this article, http://www.sqlservercentral.com/columnists/rmarda/dynamicvsstatic2.asp

It describes how to use "Case" in the order by clause.

Kathi

Thanks,  I'll take a look there. 

This is the result I get when I run the exact code.  I didn't really try the code before posting the answer, but I had used the tequique before.

Server: Msg...

I would probably do it as a stored procedure so you can add some logic. 

Here is an example if you want it to pull from the current year

create proc...

I know one way to trap the error, but you are not going to like it.  If you put your test in a dynamic SQL statement, then you can retrieve...

Update t2

set TotalCustomers = custCount

from table2 t2 join (select count(CustNumber) custCount,Shop,Branch,week

from table1

group by shop,branch,week) t1

Those triggers can be a pain.  Very powerful, but hidden from view!  I knew there had to be a logical explanation.

Very strange.  I have just created a table with the three columns you mention, populated it with some test data, and then ran your query. It ran fine. 

It looks like your query is correct.  You are using the 'IN' keyword.  Is it possible that the query you typed here is not the actual query that you tried?

This is what I would probably do.  Say you are using your existing function in hundreds of places, but you have a need for the second parameter in 10 places. ...

Unfortunately, Books On Line says that you can use default parameters, but you have to specify the keyword "default".  Yuck!

Here is an example that worked for me, but it's not...

Try this:

ALTER TABLE dbo.report_review_log WITH NOCHECK ADD 

 FOREIGN KEY (report_id, proc_name, fyear) REFERENCES dbo.report_inventory_log( report_id, proc_name, fyear ) ON DELETE NO ACTION

GO

Basically, you have to list the fields in...

Kevin did reply to me personally that my solution was correct.