Syntax will be same as well as well for the normal procedure.
Below Mention Points , Will be Applied on the procedure.
We will Create a procedure for the insert and update for the Table CountryMaster
And We Will Apply Check for the CountryCode Duplicacy
1- IF CountryCode Already Exists into table then return a message into JSON Format.
2- IF CountryCode And CountryName Inserted Successfully then return a Message into JSON Format.
3- IF CountryCode And CountryName Updated Successfully then return a Message into JSON Format.
And we will use IF , Else IF Condition to Perform mention above Points.
And you can get more knowledge from below Attached Screen Shot.
-- And you can try yourself
Note : JSON is Case Sensitive (When you compare then use Lower or upper function for the same )
Create Proc USP_InsertUpdate
(
@JsonData Nvarchar(Max)
) --USP_InsertUpdate '{"CompanyCode":"CNSyS","CompanyName":"Test1","CompanyId":"6"}'
As
Begin
Begin Try
Begin Tran
Declare @CompanyCode NVarchar(10)='',@CompanyName Nvarchar(50),@CompanyId Int
Select @CompanyCode=JSON_VALUE(@JsonData,'$.CompanyCode')
Select @CompanyName=JSON_VALUE(@JsonData,'$.CompanyName')
Select @CompanyId=JSON_VALUE(@JsonData,'$.CompanyId')
IF Exists(Select CompanyId from CompanyMaster Where Lower(CompanyCode)=@CompanyCode)
Begin
Select(Select 'Company Code already exists.' As Data for Json Path) JsonValue
End
Else If @CompanyId=0
Begin
Insert Into CompanyMaster(CompanyCode,CompanyName)
Values(Lower(@CompanyCode),Lower(@CompanyName))
Select (Select 'Inserted Succesdfully' As Data For Json Path) JsonValue
End
Else
Begin
Update CompanyMaster Set CompanyCode=Lower(@CompanyCode),CompanyName=Lower(@CompanyName)
Where CompanyId=@CompanyId
Select (Select 'Updated Succesfully' As Data For Json Path) JsonValue
End
Commit Tran
End Try
Begin Catch
Select(Select ERROR_MESSAGE() +'On Line No :'+Cast(Error_Line() As Varchar) As Data For Json Path) JsonValue
End Catch
End
š Thank You for Reading!
Thank you for taking the time to read this blog!
If you have any questions or need help with something, feel free to drop a message in the comments or contact section. Iāll get back to you as soon as possible.
Happy Learning! š
Surya Prakash (Admin) Reply
Thanks Abhishek to giving this review.