Insert and Update

Question

Hi Iam writing the stored procedure I want to insert and update the values for the one table in one qurey .I want to insert team name if teamid is not specified if teamid is specified then i will update the teamname based on id.But the problem is i didn't get any issues i will executed successfully. issue is i'm getting the insert value but the value is not updated . Here is my query.. ALTER PROCEDURE UspInsertUpdateTeam ( @TeamID INT @TeamName NVARCHAR(50) ) AS BEGIN SET NOCOUNT ON; --IF EXISTS (SELECT TeamName FROM Team ) --IF EXISTS (SELECT TeamName=NULL FROM Team) IF @TeamName='' insert into Team(TeamName)values(@TeamName); ELSE IF @TeamName='TeamName' UPDATE Team set TeamName=@TeamName where TeamID=@TeamID; END If anyone please help me. Thanks Prathysha

Sujeet Suman · Answer

You can write identity column output parameters for this procedure as below: IF NOT EXISTS ( SELECT * FROM Team WHERE TeamID = @TeamID ) INSERT INTO Team (TeamName) Output Inserted.IdentityColumnName VALUES(@TeamName); ELSE UPDATE Team SET TeamName = @TeamName where TeamID = @TeamID; END If this this is useful for you please accept my Answer .

vutharadi prathyusha · Answer

yaa it's working thanks

Sujeet Suman · Answer

Vutharadi is my query working?

vutharadi prathyusha · Answer

How to write identity column outparameters for this procedure

Sujeet Suman · Answer

Vutharadi please once make changes in your query as per below: IF NOT EXISTS ( SELECT * FROM Team WHERE TeamID = @TeamID ) INSERT INTO Team ( TeamName ) VALUES( @TeamName ); ELSE UPDATE Team SET TeamName = @TeamName where TeamID = @TeamID; END If this this is useful for you please accept my Answer .

Shashank Patil · Answer

ALTER PROCEDURE UspInsertUpdateTeam ( @TeamID INT @TeamName NVARCHAR(50) ) AS BEGIN SET NOCOUNT ON; SET IDENTITY_INSERT Table1 ON IF NOT EXISTS (SELECT TOP 1 FROM Team WHERE TeamName = @TeamName) insert into Team(TeamID TeamName)values(@TeamID @TeamName); ELSE UPDATE Team set TeamName=@TeamName where TeamID=@TeamID; SET IDENTITY_INSERT Table1 OFF END ------------------------------------------------------ If it helps Please accept my answer.

vutharadi prathyusha · Answer

sorry i'm getting error. Cannot insert explicit value for identity column in table 'Team' when IDENTITY_INSERT is set to OFF.

Shashank Patil · Answer

ALTER PROCEDURE UspInsertUpdateTeam ( @TeamID INT @TeamName NVARCHAR(50) ) AS BEGIN SET NOCOUNT ON; IF NOT EXISTS (SELECT TOP 1 FROM Team WHERE TeamName = @TeamName) insert into Team(TeamID TeamName)values(@TeamID @TeamName); ELSE UPDATE Team set TeamName=@TeamName where TeamID=@TeamID; END ------------------------------------------------------ If it helps Please accept my answer.