CREATE PROCEDURE

Question

If Row Exists Update Else Insert in Asp .net fields : StudentID FirstName LastName and Status CREATE PROCEDURE ExistsUpdateInsert ( @StudentID int  @FirstName [nvarchar] ( 50 ) @LastName [nvarchar] ( 50 ) @Status [nvarchar] ( 50 ) ) AS BEGIN IF EXISTS ( SELECT StudentID FROM StudentTable WHERE StudentID = @StudentID ) UPDATE StudentTable set StudentID = @StudentID WHERE Status = 'Active' ELSE INSERT INTO StudentTable ( StudentID  FirstName  LastName ) VALUES ( @StudentID  @FirstName  @LastName ) END END please help code

Sunny Sharma · Answer

Hi Sanjay Here you go use the code snippet below: ---------------------------------------------- CREATE PROCEDURE ExistsUpdateInsert( @StudentID int @FirstName [nvarchar](50) @LastName [nvarchar](50) @Status [nvarchar](50) ) AS BEGIN IF EXISTS (SELECT StudentID FROM StudentTable WHERE StudentID =@StudentID) UPDATE StudentTable SET FirstName=@FirstName LastName=@LastName Status=@Status WHERE Status='Active' AND StudentID=@StudentID; ELSE INSERT INTO StudentTable (StudentID FirstName LastName) VALUES (@StudentID @FirstName @LastName); END -------------------------------------------------------- Happy Coding :)

Sandeep Singh Shekhawat · Answer

Hi Sanjay The following stored procedure is okay for update and insert but I want to share few point. 1. You should not update StudentId because it is using to check for already existing record in table. 2. Status is reserved keyword in sql server so you can't directly insert any inserting if you insert the it will be null and your data will not update on basic of it. So write code for insert update by stored procedure like EXEC ExistsUpdateInsert 1  'Sandeep '  'Singh'  N'Active' 3. You are using StudentId for check exising so used it into update query the condition. 4. You should use other fields in update condition set fields instead of StudentId. 5. You should insert [Status] field in insert statement otherwise it will be null and can't perform update operation. CREATE PROCEDURE ExistsUpdateInsert ( @StudentID int  @FirstName [nvarchar] ( 50 ) @LastName [nvarchar] ( 50 ) @Status [nvarchar] ( 50 ) ) AS BEGIN IF EXISTS ( SELECT StudentID FROM StudentTable WHERE StudentID = @StudentID ) BEGIN UPDATE StudentTable set FirstName = @FirstName  LastName = @LastName WHERE [Status] = 'Active' END ELSE BEGIN INSERT INTO StudentTable ( StudentID  FirstName  LastName  [Status] ) VALUES ( @StudentID  @FirstName  @LastName  @Status ) END END

Jeetendra Gund · Answer

because of check update syntax UPDATE table_name SET column1 = value1  column2 = value2 ... WHERE some_column = some_value ; CREATE PROCEDURE ExistsUpdateInsert ( @StudentID int  @FirstName [nvarchar] ( 50 ) @LastName [nvarchar] ( 50 ) @Status [nvarchar] ( 50 ) ) AS BEGIN IF EXISTS ( SELECT StudentID FROM StudentTable WHERE StudentID = @StudentID ) UPDATE StudentTable SET StudentID = @StudentID WHERE Status = 'Active' ELSE INSERT INTO StudentTable ( StudentID  FirstName  LastName ) VALUES ( @StudentID  @FirstName  @LastName ) END END Hi try this ... Thanks..

Sanjay Kumar · Answer

Msg 102 Level 15 State 1 Procedure ExistsUpdateInsert Line 10 Incorrect syntax near 'StudentID'.

Jeetendra Gund · Answer

Tell me your exact problem then i give u solution for that.. Thanks...