Hi Nedash,
I have updated your SP as follow.
And please also note that the structure what I have implemented to this SP. Code should be written is such a way so it is very easy to read, understand and if there is any issue its quicker to fix.
Please try the following SP and let me know what value are you getting in resultMessage.
ALTER procedure [dbo].[insertproduct4]
@Name nvarchar(30)
,@Model nvarchar(30)
,@Behcode nvarchar(10)
,@price nvarchar(30)
,@Classification nvarchar(30)
,@Description nvarchar(max)
,@subset nvarchar(30)
,@id int =0
,@Result NVARCHAR(90) OUTPUT
AS BEGIN
SET NOCOUNT ON;
--DECLARE @Result INT
--SET @Result = 1
DECLARE @H_name NVARCHAR(30) SET @H_name =(SELECT H_name FROM House_info WHERE behcode=@Behcode )
DECLARE @BehcodeN NVARCHAR(30) SET @BehcodeN =(SELECT BehcodeN FROM House_info WHERE behcode=@Behcode )
IF @id > 0 AND EXISTS(SELECT behcode FROM House_p WHERE id = @id AND behcode = @Behcode)
BEGIN
UPDATE House_p
SET Name=@Name
,Model=@Model
,Description=@Description
,Price=@price
,Classification=@Classification
,subset=@subset
,Date=GETDATE()
,H_name=@H_name
,BehcodeN=@BehcodeN
WHERE BehCode=@Behcode and ID=@id
SET @Result = 'Your Produc has been updated.'
IF NOT EXISTS(SELECT Subset FROM Subset_menu WHERE Subset = @subset)
BEGIN
INSERT INTO Subset_menu(H_name,Subset)
VALUES(@H_name,@subset)
END
SELECT @id
END
ELSE
BEGIN
IF (SELECT COUNT(behcode) FROM House_p WHERE behcode = @behcode and BehcodeN='free')< 2
BEGIN
INSERT INTO House_p(Name
,Model
,Price
,Classification
,subset
,[Date]
,H_name
,BehCode
,search
,BehcodeN)
VALUES(@Name
,@Model
,@price
,@Classification
,@subset
,GETDATE()
,@H_name
,@Behcode
,@Name+' '+@Model
,@BehcodeN)
SET @Result='your product insert '
END
ELSE
BEGIN
SET @Result = 'you can just enter 2 product'
END
IF NOT EXISTS(SELECT Subset FROM Subset_menu WHERE Subset = @subset)
BEGIN
INSERT INTO Subset_menu(H_name,Subset)
VALUES(@H_name,@subset)
END
SELECT CAST(SCOPE_IDENTITY() AS INT)
END
END
Thanks and Regards,
Rk_Hirpara.
