Hello,
Seems that your ID is an Identity column, you could easily simply your code by doing something like this.
CREATE PROCEDURE [dbo].[Ins_NameNew]
(
@Name_Input varChar(Max)
)
AS
BEGIN
DECLARE @NameExist INT = 0
DECLARE @NameID INT
IF EXISTS (SELECT * FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input)
BEGIN
INSERT INTO [dBName].[dbo].[Names]
(Name)
VALUES
(@Name_Input);
SET @NameID = @@IDENTITY;
END
ELSE
BEGIN
SET @NameID = (SELECT TOP 1 ID FROM [dBName].[dbo].[Names] WHERE Name = @Name_Input)
SET @NameExist = 1;
END
SELECT @NameExist,
@NameID,
CASE
WHEN @NameExist = 0
THEN 'Added'
ELSE 'Exist'
END as 'Action'
END