Table Exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Exists

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-07-07 : 09:59:38

I am writing a stored procedure to temporarily turn off the constraint on five tables to update their key field to a different value. However, if the client is on one particular version, they do not have the 5th table yet (they only have four). Instead of writing two different sp's, I would like to write one and handle the exception inside the sp if that is possible, but I cannot figure out how to check to see if the table exists.

tblMemberPrePaid_v62 is the table that I need to check if it exists. I have read that there possibility with "IF Exists (Select 1 from tblMemberPrePaid_v62), but I cannot quit figure out the syntax or if that is even the correct way. I cannot find anything that is a function like If Table Exists?? Any thoughts!!

Thx,
JAdauto

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ctcUpdatePSKAccts]
@vs_OldMemberNumber nvarchar (48),
@vs_NewMemberNumber nvarchar(48)
AS
BEGIN

BEGIN TRANSACTION

Alter table psk64.dbo.tblSales_v62 NOCHECK CONSTRAINT FK_tblSales_v62_tblMember_v62
UPDATE psk64.dbo.tblSales_v62
Set Acct =@vs_OldMemberNumber
Where Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblSales_v62 CHECK CONSTRAINT FK_tblSales_v62_tblMember_v62

Alter table psk64.dbo.tblMemberAccount_v62 NOCHECK CONSTRAINT FK_tblMemberAccount_tblMember_v62
UPDATE psk64.dbo.tblMemberAccount_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMemberAccount_v62 CHECK CONSTRAINT FK_tblMemberAccount_tblMember_v62

Alter table psk64.dbo.tblMemberSmartCard_v62 NOCHECK CONSTRAINT FK_tblMemberSmartCard_v62_tblMember_v62
UPDATE psk64.dbo.tblMemberSmartCard_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMemberSmartCard_v62 CHECK CONSTRAINT FK_tblMemberSmartCard_v62_tblMember_v62

Alter table psk64.dbo.tblMbrCreditBook_v62 NOCHECK CONSTRAINT FK_tblMbrCreditBook_v62_tblMember_v62
UPDATE psk64.dbo.tblMbrCreditBook_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMbrCreditBook_v62 CHECK CONSTRAINT FK_tblMbrCreditBook_v62_tblMember_v62

Alter table psk64.dbo.tblMemberPrePaid_v62 NOCHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62
UPDATE psk64.dbo.tblMemberPrePaid_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMemberPrePaid_v62 CHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62

COMMIT TRANSACTION
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:04:50

[code]
if exists (select * from sys.tables where name = 'tblMemberPrePaid_v62')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-07-07 : 10:37:53

Invalid object name 'psk64.dbo.tblMemberPrePaid_v62'.

This is the error I am getting when I add that line. Below is what that section looks like now. I do not have a tblMemberPrePaid_v62, so I should not be running this section, or at least that is my goal. Am I doing it incorrectly?

Thx so much.

if exists (select * from sys.tables where name = 'tblMemberPrePaid_v62')

Alter table psk64.dbo.tblMemberPrePaid_v62 NOCHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62

UPDATE psk64.dbo.tblMemberPrePaid_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber

ALTER TABLE psk64.dbo.tblMemberPrePaid_v62 CHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 10:56:54

[code]
if exists (select * from sys.tables where name = 'tblMemberPrePaid_v62')
begin
Alter table psk64.dbo.tblMemberPrePaid_v62 NOCHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62

UPDATE psk64.dbo.tblMemberPrePaid_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber

ALTER TABLE psk64.dbo.tblMemberPrePaid_v62 CHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-07-07 : 12:04:33

This works like a charm now! Thanks so much. If I could ask one more favor? I had to build this for mutliple versions for clients who have differet versions of this companies software where one version is missing this table and then also if they had an older version which uses a completely different database. I have modified this sp even more now that I got one working correctly and I am not getting any errors, but would you mind looking briefly at it to see if you see any problems glaring. I suppose I could have written two sp's for the different versions, but I would rather keep in one to maintain if possible. Thanks so much,
JAdauto

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ctcUpdatePSKAccts]
@vs_OldMemberNumber nvarchar (48),
@vs_NewMemberNumber nvarchar(48),
@vb_UsePSK64 bit

AS
BEGIN
BEGIN TRAN

If @vb_UsePSK64 = 1

if exists (select * from sys.tables where name = 'tblSales_v62')
BEGIN
Alter table psk64.dbo.tblSales_v62 NOCHECK CONSTRAINT FK_tblSales_v62_tblMember_v62
UPDATE psk64.dbo.tblSales_v62
Set Acct = @vs_OldMemberNumber
Where Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblSales_v62 CHECK CONSTRAINT FK_tblSales_v62_tblMember_v62
END

if exists (select * from sys.tables where name = 'tblMemberAccount_v62')
BEGIN
Alter table psk64.dbo.tblMemberAccount_v62 NOCHECK CONSTRAINT FK_tblMemberAccount_tblMember_v62
UPDATE psk64.dbo.tblMemberAccount_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMemberAccount_v62 CHECK CONSTRAINT FK_tblMemberAccount_tblMember_v62
END

if exists (select * from sys.tables where name = 'tblMEmberSmartCard_v62')
BEGIN
Alter table psk64.dbo.tblMemberSmartCard_v62 NOCHECK CONSTRAINT FK_tblMemberSmartCard_v62_tblMember_v62
UPDATE psk64.dbo.tblMemberSmartCard_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMemberSmartCard_v62 CHECK CONSTRAINT FK_tblMemberSmartCard_v62_tblMember_v62
END

if exists (select * from sys.tables where name = 'tblMbrCreditBook_v62')
BEGIN
Alter table psk64.dbo.tblMbrCreditBook_v62 NOCHECK CONSTRAINT FK_tblMbrCreditBook_v62_tblMember_v62
UPDATE psk64.dbo.tblMbrCreditBook_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMbrCreditBook_v62 CHECK CONSTRAINT FK_tblMbrCreditBook_v62_tblMember_v62
END

if exists (select * from sys.tables where name = 'tblMemberPrePaid_v62')
BEGIN
Alter table psk64.dbo.tblMemberPrePaid_v62 NOCHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62
UPDATE psk64.dbo.tblMemberPrePaid_v62
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk64.dbo.tblMemberPrePaid_v62 CHECK CONSTRAINT FK_tblMemberPrePaid_v62_tblMember_v62
END

Else

if exists (select * from sys.tables where name = 'tblSales')
BEGIN
Alter table psk5msdesql.dbo.tblSales NOCHECK CONSTRAINT FK_tblSales_tblMember
UPDATE psk5msdesql.dbo.tblSales
Set Acct = @vs_OldMemberNumber
Where Acct = @vs_NewMemberNumber
ALTER TABLE psk5msdesql.dbo.tblSales CHECK CONSTRAINT FK_tblSales_tblMember
END

if exists (select * from sys.tables where name = 'tblMemberAccount')
BEGIN
Alter table psk5msdesql.dbo.tblMemberAccount NOCHECK CONSTRAINT FK_tblMemberAccount_tblMember
UPDATE psk5msdesql.dbo.tblMemberAccount
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk5msdesql.dbo.tblMemberAccount CHECK CONSTRAINT FK_tblMemberAccount_tblMember
END

if exists (select * from sys.tables where name = 'tblMemberSmartCard')
BEGIN
Alter table psk5msdesql.dbo.tblMemberSmartCard NOCHECK CONSTRAINT FK_tblMemberSmartCard_tblMember
UPDATE psk5msdesql.dbo.tblMemberSmartCard
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk5msdesql.dbo.tblMemberSmartCard CHECK CONSTRAINT FK_tblMemberSmartCard_tblMember
END

if exists (select * from sys.tables where name = 'tblMbrCreditBook')
BEGIN
Alter table psk5msdesql.dbo.tblMbrCreditBook NOCHECK CONSTRAINT FK_tblMbrCreditBook_tblMember
UPDATE psk5msdesql.dbo.tblMbrCreditBook
Set Acct = @vs_OldMemberNumber
WHERE Acct = @vs_NewMemberNumber
ALTER TABLE psk5msdesql.dbo.tblMbrCreditBook CHECK CONSTRAINT FK_tblMbrCreditBook_tblMember
END

IF @@ERROR <> 0
ROLLBACK TRAN
Else
COMMIT TRAN
END

JAdauto
Posting Yak Master

160 Posts

Posted - 2008-07-08 : 14:17:53

Is this part of my sp correct?

IF @@ERROR <> 0
ROLLBACK TRAN
Else
COMMIT TRAN

When I run this query, I do not get any errors, but when I do a select on my previous number, it still exists. It never updated those tables. I have not worked with transaction processing in a stored procedure before, so I am unsure if I am doing this correctly. Any thoughts?

Thx for all your advice,
JAdauto

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources