How to create stored procedure for Country, State, City for saving in SQL database?

Create database USERS

USE USERS

Create table TblCountry(CId int identity(1,1) primary key,Country varchar(50))

Create table TblState(SId int identity(1,1) primary key,FK_CountryId int references TblCountry(CId),State varchar(50))

Create table TblCity(CityId int identity(1,1) primary key,FK_StateId int references TblState(SId),FK_CountryId int,City varchar(50))

create proc abc
(
@country varchar(100),
@state varchar(100),
@city varchar(200)
)
as
begin
if not exists(select * from TblCountry where country=@country)
begin
insert into TblCountry(country) values(@country)
end
declare @countryid int;
select @countryid=CId from TblCountry where country=@country
if not exists(select * from TblState where State=@state)
begin
insert into TblState(FK_CountryId,state) values(@countryid,@state)
end

declare @stateid int;
select @stateid=SId from TblState where state=@state
if not exists(select * from TblCity where city=@city)
begin
insert into TblCity(FK_StateId,FK_CountryId,city)
values(@stateid,@countryid,@city)
end

end


select * from TblCountry
select * from TblState
select * from TblCity

execute dbo.abc 'America','LansAngile','Gaziabad'

Share: