TSQL Can I do this ?

Question

TSQL Can I do this ?

Digs

SSCertifiable

Points: 7333
More actions
March 20, 2005 at 10:34 pm

#164880

Subject : TSQL and code reuse
Ok I am used to DAO in access and looping thru MS Access objects to to do something with all tables in the database. Thats is re using code for different objects. I know TSQL is static, and dynamic SQL is to be avoided.
In My SQL 2000 database I have
10 tables all with some common fields
Say
Tbl1 Sku, Customer
Tbl2 Sku, Customer
Tbl3 Sku
Tbl4 Sku, Customer
Tbl5 Sku
etc
I have a [Sku Definitions].sku table that they all link to via Foreign key
Can I use sysobjects ( or something ) to loop thru tables to write and execute the same sql on each table, OR do I have to literally write out the code 10 times
SELECT Sku FROM [tbl1]
INNER JOIN tbl1.sku ON [Sku Definitions].Sku WHERE [Sku Definitions].Sku is NULL
What to do ???

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Unfortunately, you need to either write 10x OR Dynamic SQL. The good thing is you could build a stored-procedure and call that 1x.....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Digs,

see if my reply at

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=168681

can help you. It was for the question that was about looping through tables. I replied there:

I would use sp_MSforeachtable undocumented stored procedure.

There is also a similar procedure sp_MSforeachdb see

http://www.sqlservercentral.com/columnists/achigrik/sql2000.asp

Yelena

Regards,Yelena Varsha

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 3

Also, watch out for black and white statements, like:

dynamic SQL is to be avoided.

Generally, for regularly repeated tasks that you want maximum performance out of, this is true. When working with data that a user may have tampered with, that's true.

For one-off or rare administrative tasks that can only be run be trusted personnel (for maximum safety, that means you only), dynamic SQL isn't necessarily the worst idea in the world. If no one else's inputs are allowed near the code, SQL injection issues aren't relevant; if the task is rarely done, and is not highly time sensitive, a dynamic SQL solution that takes you ten minutes to figure out and implement is probably better than a solution that takes you two hours to implement, even if the two hour solution is twice as fast.

R David Francis