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

David,

Okay, let's make this example a little more difficult and see if we
can't find something outside a system table. Let's say the table is
now the order details table, and Billy needs quartile values of unit
price from each Northwind database.  I didn't say it would be a good
example. Anyway, I'm still stuck appending things to my temp table if
I want to keep static SQL. As I didn't even think of your approach
when Billy first suggested his example, I am really curious what your
approach would be.

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 David Lewis
> Sent: Wednesday, June 30, 2004 11:26 AM
> To: dba-sqlserver at databaseadvisors.com
> Cc: tuxedo_man at hotmail.com
> Subject: [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
<snip>