[dba-SQLServer] Difference between views and queries

Billy,

As you suggest, you could do a stored procedure for each permutation
in your example, but this is unrealistic. However, you could have one
stored procedure which would fill a temporary table with the necessary
counts based on a parameter value list using IF statements.

Your summary isn't quite right. I am not stating that one should not
say certain "coding methods" are never needed. I am sure there are
techniques that you would never get to agree to use. I am stating
that there are a known group of problems for which dynamic sql is
often, but not always, the best solution.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Billy Pang
> Sent: Wednesday, June 30, 2004 12:57 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer] Difference between views and queries
> 
> 
> oh boy, can't wait to get into this discussion :)...there is 
> one example I 
> can think of..  the example would be based on the physical 
> storage of the 
> sproc in a single db.  it is not possible for a sproc to 
> query table in 
> another db that the sproc does not reside in.
> 
> To illustrate, let's say that there are three copies of the 
> northwind db on 
> db server: northwind0, northwind1 and northwind2; all three 
> have exact db 
> schema;
> 
> Your goal is to develop a report that counts number of records in the 
> products table in each of the northwind databases.  sometimes 
> you may only 
> want to count records in northwind0 and northwind1 but not 
> northwind2; 
> sometimes you may only want northwind2 and northwind1 but not 
> northwind0; 
> and sometimes you want all three northwind dbs in your count.  It is 
> possible to create a sp for each of the permutation but if a fourth 
> northwind db is introduced to the system, then the code base 
> is doubled.
> 
> The alternate solution would be to build dynamic sql to piece 
> together 
> select statement referencing tables using the 
> [db].[owner].[table] format; 
> which tables are pieced together is based on what the user selects as 
> criteria for report.  Not elegant solution but it is a lot 
> less code to 
> maintain.
> 
> With that being said, dynamic sql is not as safe for 
> reporting compared to 
> sproc.
> 
> I think what Francis is trying to get at is that it is not 
> possible for a 
> developer to claim that a particular "coding method" is never 
> needed.  
> Rather, it is more correct to state that it is not likely 
> that a particular 
> "coding method" is ever needed.  Difference between the words 
> "impossible" 
> and "improbable".  Similiar to how a judge cannot hand out a 
> verdict to 
> court case before the case is presented no matter obvious it may seem.
> 
> Another point of reference on this topic would be look up 
> "Validating User 
> Input" in BOL for best practices on this issue.
> 
> Billy
<snip>