Help W/Disabling FK Constraints for Batch Operations

HI all,

I'm trying to have a SProc that will initialize a database for me. This db is in development (I'm primarily writing SSIS packages, atm), and I constantly need to truncate the tables, then re-add a dummy/unknown row (PK/Identity value = 1). Of course, I need triggers not to fire (got that part working), and FK constraints to be bypassed temporarily -- that's the problem.

Here's where I'm at:

-
CREATE PROCEDURE [dbo].[_InitializeDB]
AS

SET NOCOUNT ON

DECLARE @.name varchar(255)
DECLARE @.sql nvarchar(255)

DECLARE tables CURSOR FOR SELECT [name] FROM [sysobjects] WHERE [type]='U' AND [name]<>'sysdiagrams'

OPEN tables
FETCH NEXT FROM tables INTO @.name
WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.sql = 'ALTER TABLE ['+ @.name + '] NOCHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
SET @.sql = 'DISABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'TRUNCATE TABLE [' + @.name + ']'
EXEC sp_executesql @.sql
BEGIN TRY
SET @.sql = 'INSERT INTO [' + @.name + '] (Active) VALUES (0)'
EXEC sp_executeSQL @.sql
END TRY
BEGIN CATCH
PRINT @.sql + ':'
PRINT ERROR_MESSAGE()
END CATCH
SET @.sql = 'ENABLE TRIGGER ALL ON [' + @.name + ']'
EXEC sp_executeSQL @.sql
SET @.sql = 'ALTER TABLE ['+ @.name + '] CHECK CONSTRAINT ALL'
EXEC sp_executeSQL @.sql
FETCH NEXT FROM tables INTO @.name
END

CLOSE tables
DEALLOCATE tables
-

Running this Sproc produces (for the first ref'd table):

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Person' because it is being referenced by a FOREIGN KEY constraint.

If you dont wanna be bothered by the FK Constraints you have to drop them do your work and recreate them after you did your work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||TRUNCATE TABLE doesn't check the state of the FK constraint so this method will not work. You will have to drop and recreate the FK constraint for the truncate table to work.

HELP w/ Stored Procedure

Can someone explain what I'm doing wrong?

I am passing values to the SPROC and am trying to do the following:

1.) I check to see if the record already exists in the MEMBERS table
NO -> I want to insert a new record and then set @.MEMBER_ID = SCOPE_IDENTITY
YES-> I want to skip the insert and set @.MEMBER_ID to the existing MEMBER_ID

2.) I check to see if the record already exists in the SW_REQUESTS table
NO -> I want to insert a new record (Insert @.MEMBER_ID)
YES -> I want to skip the insert and close out the SPROC

Here is my code that doesn't work. I get an error when setting @.MEMBER_ID = MEMBER_ID:

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0'
AS
SET NOCOUNT ON
IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO

You need to declare @.Member_ID as an OUTPUT parameter, if you expect to get it back:

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT

MEMBERS does have a IDENTITY column, correct?

This section of code:

IF EXISTS
(SELECT MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SET @.MEMBER_ID = MEMBER_ID
END

should be like this:

IF EXISTS
(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END

You need to actually select the @.Member_ID value somewhere. Alternately, you could do the select where @.Member_ID=Member_ID outside the IF and then check IsNull(@.Member_ID,0)<>0|||I do have a IDENTITY column in the table MEMBERS. It is MEMBER_ID. When I made the changes you suggested, I still get an error message "Invalid Column Name MEMBER_ID"

Here is the code:

CREATE PROCEDURE b4b_sw_request
@.FName as varchar(50)= NULL,
@.LName as varchar(50)=NULL,
@.Address1 as varchar(100) = NULL,
@.Address2 as varchar(100) = NULL,
@.City as varchar(50) = NULL,
@.State as char(2) = NULL,
@.Zip as char(5) = NULL,
@.Email as varchar(100) = NULL,
@.Send_Updates as smallint = '0',
@.MEMBER_ID as int = '0' OUTPUT
AS
SET NOCOUNT ON
IF EXISTS
(SELECT * from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip)
BEGIN
SELECT @.Member_ID=MEMBER_ID from MEMBERS WHERE FName = @.FName AND LName = @.LName AND Address1 = @.Address1 AND Zip = @.Zip
SET @.MEMBER_ID = MEMBER_ID
END
ELSE
BEGIN
INSERT INTO MEMBERS
(FName, LName, Address1, Address2, City, State, Zip, Email)
VALUES
(@.FName, @.LName, @.Address1, @.Address2, @.City, @.State, @.Zip, @.Email)
SELECT @.MEMBER_ID=SCOPE_IDENTITY()
END
IF EXISTS
(SELECT MEMBER_ID FROM SW_REQUESTS WHERE MEMBER_ID = @.MEMBER_ID)
BEGIN
Print ''
END
ELSE
BEGIN
INSERT INTO SW_REQUESTS
(MEMBER_ID, Send_Updates)
Values
(@.MEMBER_ID, @.Send_Updates)
END
GO

|||Sorry. Loose this line:

SET @.MEMBER_ID = MEMBER_ID

If you still get an error, please specify exactly what line gived the error.|||Thanks Doug. I had just removed it and tested it when I got your message. Works great!