[dba-SQLServer] RE: Difference between views and queries (Billy Pang)

Hi All:

Regarding Billy Pang's comment:

> .  it is not possible for a sproc to 
> query table in 
> another db that the sproc does not reside in.

you note later in your comment that the syntax [db].[objectowner].[objectname] will make the above possible.
So, to avoid dynamic sql for the problem you've posed one could do the following:

1) if the number/names of the databases is not static, the user interface (let's assume it is web based) could query the server or servers and build a list of all available databases.

2) if the tables to be examined are another variable, the next step would be to present a list of [db]..[tablename] from each database selected.

3)a comma delimited string of [database].[objectowner].[tablename] could be delivered to a sproc, which would then parse it (a udf can accomplish this, or it could be done directly in the sproc), after which  the appropriate system tables could be queried.

This is not to say that dynamic sql is never necessary, but I think the above approach would allow one to avoid it (if that was the goal) for the problem you've posed.

P.S. I think Billy's question has made the dynamic sql vs. stored procedures topic practical.  It was beginning to get a touch dogmatic, and, dare I say it, personal ...  :)


Kind regards to all.  D. Lewis

> Message: 3
> Date: Wed, 30 Jun 2004 04:56:50 +0000
> From: "Billy Pang" <tuxedo_man at hotmail.com>
> Subject: RE: [dba-SQLServer] Difference between views and queries
> To: dba-sqlserver at databaseadvisors.com
> Message-ID: <BAY1-F16dBdsnvUFcxT00000972 at hotmail.com>
> Content-Type: text/plain; format=flowed
> 
> 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
>