Regarding Dynamic query...

Hi,

I have basic design question regarding dynamic query,

When we have to build a dynamic query (which has table name also as an input parameter),

->Is it better to write a stored procedure ..?

or

->directly specify the dynamic query and set the command type as text in .NET code...?

I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..

Please through some light on this,

TIA

I don't think there's any right answer to this question. Personally I'd prefer to build a stored procedure for this because I like all of my database access code to be centrally located. Others will say it's a waste of time and effort to put this type of dynamic code into a stored procedure because it buys you nothing in terms of performance and it litters the database unnecessarily.

This is the type of topic that can generate a lot of debate. We'll see if anyone else bites. :-)

|||Why do you need to pass in a table name? Why does yourapplication have that much knowledge of your database? It soundsas though you have a serious architectural problem. You'vetightly coupled your application to your database. Figure out howto DECOUPLE the systems so that changing one won't break theother. One of the main benefits of stored procedures isencapsulation; passing in a table name defeats that goal.

sql

Referential integrity during replication

Hello forum

I like to build a custom replication application for a databasee.
To questions concering this. The idea is to use a mechanim, which updates table by table (in sequence).

During the replication proccess I update a table (table1), which has 1 to 1 refenceto a second table(table2). This new record is unsing a reference which hasn't been entered by this time into table 2. This is hurts the referencial integrity, right?

Would it help to use a transaction for the update in table 1 and table 2, when is the integrity checked? At the end of the transaction, by the command commit transaction?

Is it possible to switch on/off the referential integrity check while a database is running? If yes, are there any side effects beside that the integrity is not propre controlles anymore?

Thanks for your help in advance!

DominikWhat about this:

drop table test1
drop table test2
go
create table test2(id int primary key)
create table test1(id int, rid int)
ALTER TABLE test1 WITH NOCHECK ADD CONSTRAINT
FK1 FOREIGN KEY(rid) REFERENCES dbo.test2(id)
go
insert test2 values(1)
insert test1 values(1,1)
go
ALTER TABLE dbo.test1 NOCHECK CONSTRAINT FK1
go
insert test1 values(1,11) -- works