Prev: SQL 2008 intellisense errors Invalid Object
Next: how to convert safearrary COM call
From: Stefan Hoffmann on 16 Nov 2009 09:10
hi @ll,

I have a question about the usage of ALTER TABLE in a stored procedure
(SQL Server 2005):

First concurrent query, started first, should be later encapsulated in a
stored procedure:

BEGIN TRANSACTION [Test] ;
ALTER TABLE [myTable]
NOCHECK CONSTRAINT ALL ;

-- Function from BOL, WAITFOR DELAY wrapper.
EXEC TimeDelay_hh_mm_ss '00:00:10';

ALTER TABLE [myTable]
CHECK CONSTRAINT ALL ;
COMMIT TRANSACTION [Test] ;


Second concurrent query:

INSERT INTO [myTable]
(
[Id],
[Abbreviation],
[Name]
)
SELECT NEWID(),
[Abbreviation],
[Name]
FROM [myTable] ;

When running it in SSMS it produces the desired result. The first query
grabs a schema lock and blocks the second query as long it runs. The
second query is executed after the commit and is denied as there are
unique constraints on [Abbreviation] and [Name].

Can I use the ALTER TABLE statement in a sproc like this:

CREATE PROCEDURE [mySproc] ()
AS
BEGIN

ALTER TABLE [myMasterTable]
NOCHECK CONSTRAINT ALL ;
ALTER TABLE [myDetailTable]
NOCHECK CONSTRAINT ALL ;

-- modify myMasterTable
-- modify myDetailTable

ALTER TABLE [myMasterTable]
CHECK CONSTRAINT ALL ;
ALTER TABLE [myDetailTable]
CHECK CONSTRAINT ALL ;

END ;

In fact I'm trying to insert a entire bunch of records in different
tables having a complex referential integrity graph.

Are there any pitfalls?

mfG
--> stefan <--
 | 
Pages: 1
Prev: SQL 2008 intellisense errors Invalid Object
Next: how to convert safearrary COM call