Needed help in Query

Hai

1. Is it possiable to delete a record from the parent table.It is
even ok to me , if it leads to the deletion of all the child tables .

2.I 've come across a situiation where the name of the table is to be
supplied by the variable in my sp ,like

@.t = 'table1'
select * from @.t -- it gives the error.

--actually i want "select * from table1" & in my sp i gave as

So i am forced to give the table name from the sub query.

how can i acheive this thru query where the table name has to be
supplied to the from clause from the sub query

With Thanks
Raghuraman.C"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0401152012.7e068064@.posting.google.c om...
> Hai
> 1. Is it possiable to delete a record from the parent table.It is
> even ok to me , if it leads to the deletion of all the child tables .
>
> 2.I 've come across a situiation where the name of the table is to be
> supplied by the variable in my sp ,like
>
> @.t = 'table1'
> select * from @.t -- it gives the error.
> --actually i want "select * from table1" & in my sp i gave as
> So i am forced to give the table name from the sub query.
> how can i acheive this thru query where the table name has to be
> supplied to the from clause from the sub query
> With Thanks
> Raghuraman.C

I'm not sure if I understand your questions completely, but I think this is
what you want:

1. If possible, you can declare your foreign keys with ON DELETE CASCADE
(see Books Online). Then when you delete a row from the parent table, any
child records will automatically be deleted also. If that isn't a good
solution in your situation, you can consider triggers, or doing all deletes
through a stored procedure which deletes rows from the tables in the correct
order.

2. See this link:

http://www.sommarskog.se/dynamic_sql.html

Simon|||Dear simon,

For the option 1.

I could not reach any word combinations like DELETE ON CASCADE for the
foriegn keys in the books on line in sqlserver 7.0. Are u telling with
SQLSERVER2000.

If so, what the way in sqlserver7.0

With regards
Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Raghu Raman <raghuraman_ace@.rediffmail.com> wrote in message news:<400cd41a$0$70304$75868355@.news.frii.net>...
> Dear simon,
> For the option 1.
> I could not reach any word combinations like DELETE ON CASCADE for the
> foriegn keys in the books on line in sqlserver 7.0. Are u telling with
> SQLSERVER2000.
> If so, what the way in sqlserver7.0
> With regards
> Raghu
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Yes, cascading DRI is only available in SQL2000. I should have
mentioned that, but please always state which version of MSSQL you're
using. In SQL7, you can either use triggers or stored procedures. If
you can ensure that your applications will always use a stored proc
for deletions, then it is probably an easier solution. But if you have
different applications/clients, and you can't be sure that they will
always use the proc, then a trigger is more reliable.

Simon|||Hai,

I did that cascading deletion using sproc..& it is nice

Thanks for concurrent reply

With Regards
Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> 1. Is it possible to delete a record [sic] from the parent table.
<<

Tables are not files; rows are not records; columns are not fields.
They are compltely different concepts! There are "referenced" and
"referencing" tables in SQL. That "parent" and "child" terminology
belongs to network DBMS models.

You can set up DRI actions that will cascade a deletion from a
referenced table to all the referencing tables.

>> 2.I 've come across a situiation where the name of the table is to
be
supplied by the variable in my stored procedure ... <<

NO! The short answer is use slow, proprietrary dynamic SQL to kludge
a query together on the fly with your table name in the FROM clause.

The right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your applications a disaster of design.

1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.

2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc.

3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw.

4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and
applications do not work at that level, if you want to have any data
integrity.

Yes, you can write a program with dynamic SQL to kludge something like
this. it will last about a year in production and then your data
integrity is shot.