Here is an approach. I have modified your stored procedure with the comments indicating what i have done.
create PROCEDURE Country
(
@Id1 int,
@name varchar(50),
@checkCondType varchar(10), ---Country
@tbCond varchar(15)
)
AS
BEGIN
--Declare a variable to identify if its insert or update operation
DECLARE @IsInsert BIT
if @checkCondType='Country'
BEGIN
if @tbCond='Insert'
BEGIN
if not exists(select * from Country where CoName=@name)
BEGIN
if exists(select * from Country)
BEGIN
select @AutoId=MAX(CoId)+1 from Country
END
else
BEGIN
set @AutoId=1
END
END
ELSE
BEGIN
insert into Country
(
CoId,CoName
)
VALUES
(
@AutoId,@name
)
END
--Set @IsInsert to true as its an insert
SET @IsInsert = 1
END
ELSE IF @tbCond='Update'
BEGIN
update Country
set
CoName=@name where CoId=@Id1
--Set @IsInsert to false as its an Update
SET @IsInsert = 0
END
--RETURN THE @IsInsert value
SELECT @IsInsert AS OperationType
END
You can then use
SqlCommand.ExecuteScalar Method[
^] to retrieve the value from the stored procedure in your application.
I also noticed that you are manually incrementing the Id value and then inserting the new value into your CoId column. You can use
Identity[
^] property in SQL server that will automatically generate consecutive numbers as you insert data into your rows. Using Identity is always a better option.
Also looking at your stored procedure it looks like you are incrementing the CoId in the if block and inserting the data into your table in the else block. I think the data may not get inserted. I might be wrong, but just thought will let you know.