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 SumanPosted Sep 23, 2015, 8:58 AM
INSERT INTO Team (TeamName) Output Inserted.IdentityColumnName VALUES(@TeamName);
UPDATE Team SET TeamName = @TeamName where TeamID = @TeamID;
END
vutharadi prathyushaPosted Sep 23, 2015, 8:48 AM
Sujeet SumanPosted Sep 23, 2015, 8:47 AM
vutharadi prathyushaPosted Sep 23, 2015, 8:44 AM
Sujeet SumanPosted Sep 23, 2015, 5:52 AM
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
Shashank PatilPosted Sep 23, 2015, 5:46 AM
(
@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
If it helps, Please accept my answer.
vutharadi prathyushaPosted Sep 23, 2015, 5:30 AM
Shashank PatilPosted Sep 23, 2015, 5:26 AM
(
@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.