Insert FK if not present – SQL

Alter PROCEDURE dbo.EnterLoginData(@Username VARCHAR(50), @Password VARCHAR(50),
@ContactNo INT, @StateName VARCHAR(50))
AS BEGIN
DECLARE @StateID INT

— check if state already exists
IF EXISTS(SELECT * FROM dbo.DGState WHERE DG_State = @StateName)

— if it exists – retrieve the DG_StateNo
SELECT @StateID = DG_StateNo
FROM dbo.DGState
WHERE DG_State = @StateName

ELSE BEGIN

— if it doesn’t exists – insert new row
INSERT INTO dbo.DGState(DG_State) VALUES(@StateName);

— get the newly inserted row’s ID using SCOPE_IDENTITY()
SELECT @StateID = SCOPE_IDENTITY()
END

INSERT INTO
dbo.DGRegion(DG_Username, DG_Password, DG_Contactno, DG_StateNo)
VALUES(@Username, @Password, @ContactNo, @StateID)
END

exec dbo.EnterLoginData @username = ‘user’ , @Password = ‘pass’ , @ContactNo = ‘123’ ,@StateName = ‘delhi’

insert into dgregion values (‘user’,’pass’,’123′,1)

select * from DGRegion
Select * from DGState

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s