How would you modify the following SQL Server 2008 stored procedure to prevent it's execution if the value being inserted into the field "BRANCH" is a duplicate ? Branch is the Primary Key.

zimmer9
zimmer9 used Ask the Experts™
on
How would you modify the following SQL Server 2008 stored procedure to prevent it's execution if the value being inserted into the field "BRANCH" is a duplicate value because "branch" is the Primary Key?

ALTER PROC [dbo].[proc_CSL_Insert_branch]
(@strBranch varchar(3), @strRegion varchar(2), @txtRollup varchar(6), @txtCenter varchar(6),
@txtManager varchar(45), @txtBraddress varchar(50), @txtCity  varchar(35), @txtState varchar(2), @txtZip varchar(10),
@txtPhone varchar(12), @txtFax varchar(12), @txtAssistant varchar(35), @txtMemo varchar(50),  
@txtBrname varchar(50), @txtEmailaddr varchar(50), @txtType varchar(1), @chkStatus bit)  
AS
INSERT INTO dbo.tbl_CSL_Branches (branch, region, rollups, center,  manager, Braddress, city,  states,  zip,
phone, fax,  assistantnm,  memo, branchname, EMAILADD, typ,  closed)
VALUES (@strBranch, @strRegion, @txtRollup, @txtCenter, @txtManager, @txtBraddress, @txtCity, @txtState, @txtZip,
@txtPhone, @txtFax, @txtAssistant, @txtMemo, @txtBrname, @txtEmailaddr, @txtType, @chkStatus)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>prevent it's execution if the value being inserted into the field "BRANCH" is a duplicate value because "branch" is the Primary Key?

IF NOT EXISTS (SELECT Branch FROM dbo.tbl_CSL_Branches WHERE Branch = @strBranch) 
   begin 
   -- Your INSERT block goes here
   end

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Do you want to return something? Just replace the PRINT code to what you want to be returned:
ALTER PROC [dbo].[proc_CSL_Insert_branch]
	(@strBranch varchar(3), @strRegion varchar(2), @txtRollup varchar(6), @txtCenter varchar(6),
	@txtManager varchar(45), @txtBraddress varchar(50), @txtCity  varchar(35), @txtState varchar(2), @txtZip varchar(10), 
	@txtPhone varchar(12), @txtFax varchar(12), @txtAssistant varchar(35), @txtMemo varchar(50),  
	@txtBrname varchar(50), @txtEmailaddr varchar(50), @txtType varchar(1), @chkStatus bit)  
AS

BEGIN TRY
	INSERT INTO dbo.tbl_CSL_Branches (branch, region, rollups, center,  manager, Braddress, city,  states,  zip, phone, fax,  assistantnm,  memo, branchname, EMAILADD, typ,  closed)
	VALUES (@strBranch, @strRegion, @txtRollup, @txtCenter, @txtManager, @txtBraddress, @txtCity, @txtState, @txtZip, @txtPhone, @txtFax, @txtAssistant, @txtMemo, @txtBrname, @txtEmailaddr, @txtType, @chkStatus) 
END TRY

BEGIN CATCH
	IF @@ERROR = 2627
		PRINT 'PK violation'
	ELSE
		PRINT 'Other error'
END CATCH

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial