Search code examples
c#sql-serverstored-proceduresbulkinsertinsert-update

C# stored procedure insert or update with DataTable problem?


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  

Solution

  • @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