create table :-
CREATE TABLE [dbo].[TBL_CATEGORY_MASTER](
[pk_cat_id] [int] IDENTITY(1,1) NOT NULL,
[category_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[is_active] [bit] NULL CONSTRAINT [DF_TBL_CATEGORY_MASTER_is_active] DEFAULT ((1)),
CONSTRAINT [PK_TBL_CATEGORY_MASTER] PRIMARY KEY CLUSTERED
(
[pk_cat_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
then
create proc [dbo].[usp_Add_Category]
@pk_cat_id int,
@category_name varchar(50),
@active int
as
if exists(select 1 from TBL_CATEGORY_MASTER where category_name=@category_name AND pk_cat_id<>@pk_cat_id)
BEGIN
SELECT -1
end
ELSE if exists (select 1 from TBL_CATEGORY_MASTER where pk_cat_id=@pk_cat_id)
BEGIN
UPDATE TBL_CATEGORY_MASTER SET category_name=@category_name,is_active=@active WHERE pk_cat_id=@pk_cat_id
SELECT 0
end
ELSE if (@pk_cat_id=0)
BEGIN
INSERT INTO TBL_CATEGORY_MASTER VALUES (@category_name,@active)
select @@identity
END
if stored procedure return -1 then record already exists...
try this query :)
you will get all your answers