I want to add or update with the code below. I get the following error. How should I edit this code?
Must declare the scalar variable "@UyeID".
Incorrect syntax near the keyword 'ELSE'.
My code:
CREATE TYPE [dbo].[BcgTarihiType] AS TABLE
(
[UyeID] int NOT NULL,
[BcgTarihi] [nvarchar](30) NULL
)
GO
CREATE PROCEDURE Insert_BcgTarihleri
(@tblBcgTarihleri BcgTarihiType READONLY)
AS
BEGIN
IF EXISTS (SELECT 1 FROM BcgTarihleri
WHERE UyeID = @UyeID AND BcgTarihi = @BcgTarihi)
BEGIN
UPDATE BcgTarihleri
SET UyeID = @UyeID, BcgTarihi = @BcgTarihi
WHERE UyeID = @UyeID
END
ELSE
BEGIN
INSERT INTO BcgTarihleri
VALUES (@UyeID, @BcgTarihi)
END
END
I also want to ask the question in this way. The code below works but I can only add.How can this update if exist data?
CREATE TYPE [dbo].[BcgTarihiType] AS TABLE(
[UyeID] int NOT NULL,
[BcgTarihi] [nvarchar](30) NULL
)
GO
CREATE PROCEDURE Insert_BcgTarihleri
(
@tblBcgTarihleri BcgTarihiType READONLY
)
AS
BEGIN
INSERT into BcgTarihleri (UyeID,BcgTarihi)
SELECT UyeID, BcgTarihi FROM @tblBcgTarihleri;
END
@Boris Makhlin, @Dale K Thank you for your help I solved my problem with the code below
CREATE TYPE [dbo].[BcgTarihiType] AS TABLE(
[UyeID] int NOT NULL,
[BcgTarihi] [nvarchar](30) NULL
)
GO
CREATE PROCEDURE [dbo].[Update_BcgTarihleri]
@tblBcgTarihleri BcgTarihiType READONLY
AS
BEGIN
SET NOCOUNT ON;
MERGE INTO BcgTarihleri b1
USING @tblBcgTarihleri b2
ON b1.UyeID = b2.UyeID AND b1.BcgTarihi = b2.BcgTarihi
WHEN MATCHED THEN
UPDATE SET b1.UyeID = b2.UyeID
,b1.BcgTarihi = b2.BcgTarihi
WHEN NOT MATCHED THEN
INSERT VALUES(b2.UyeID, b2.BcgTarihi);
END