Procedure inserts only one record

Question

I have this stored procedure:

CREATE PROCEDURE [dbo].[TVP_OfferPrice] @OfferPriceTVP TVP_OfferPrice READONLY
AS 
BEGIN
    DECLARE @OfferId INT;
    DECLARE @CountryId INT ;
    DECLARE @VatRateId INT ;
    DECLARE @SalePrice DECIMAL(16, 4) ;
    DECLARE @SaleFromDate DATETIME;
    DECLARE @SaleToDate DATETIME;
    DECLARE @DefaultPrice DECIMAL(16, 4);
    DECLARE @Price DECIMAL(16,4);

    SELECT  
        @OfferId = a.OfferId, @CountryId = a.CountryId, @VatRateId = a.VatRateId,
        @SalePrice = a.SalePrice, @SaleFromDate = a.SaleFromDate, @SaleToDate = a.SaleToDate, 
        @DefaultPrice =a.DefaultPrice 
    FROM 
        @OfferPriceTVP a;

    SET @Price = (SELECT TOP 1 pp.Price  
                  FROM [dbo].[Promotion] p 
                  INNER JOIN [dbo].[PromotionProduct] pp ON pp.ProductId = p.Id
                  INNER JOIN [dbo].[Offer] do ON do.ProductId = pp.ProductId AND do.Id = @OfferId 
                  INNER JOIN [dbo].[PromotionAssignment] pda ON pda.PromotionId = p.Id AND pda.Id = do.Id 
                  WHERE p.CountryId = @CountryId
                    AND GETUTCDATE() >= p.ValidFrom AND GETUTCDATE() < p.ValidTo 
                  ORDER BY p.ValidFrom DESC, pp.Price)

    IF(@Price IS NULL AND @SalePrice IS NOT NULL AND GETUTCDATE() >= @SaleFromDate AND GETUTCDATE() < @SaleFromDate)
        SET @Price = @SalePrice

    IF @Price IS NULL
        SET @Price = @DefaultPrice

    IF NOT EXISTS (SELECT * FROM [dbo].[OfferPrice] dop  WHERE dop.OfferId = @OfferId AND dop.CountryId = @CountryId)
        INSERT INTO [dbo].[OfferPrice](OfferId, CountryId, VatRateId, Price, DefaultPrice, SalePrice, SaleFromDate, SaleToDate)
           SELECT 
              @OfferId, @CountryId, @VatRateId, @Price, @DefaultPrice, 
              @SalePrice, @SaleFromDate, @SaleToDate 
    ELSE
        UPDATE b 
        SET b.VatRateId = @VatRateId, @Price = @Price, b.DefaultPrice = @DefaultPrice, 
            b.SalePrice = @SalePrice, b.SaleFromDate = @SaleFromDate, b.SaleToDate = @SaleToDate 
        FROM
            [dbo].OfferPrice b 
        WHERE 
            b.OfferId = @OfferId AND b.CountryId = @CountryId;
END

and when I try to execute it with some values for example:

DECLARE @OfferPriceTVP AS [dbo].[TVP_DealerOfferPrice]

INSERT INTO @OfferPriceTVP (DealerOfferId, CountryId, VatRateId, DefaultGrossPrice, SaleGrossPrice, SaleFromDate, SaleToDate)
VALUES (10006805, 1, 1, 1, 1, 2, NULL),
(10006806, 1, 1, 2, 1, NULL, NULL),
(10006807, 1, 1, 3, 1, NULL, NULL),
(10006808, 1, 1, 4, 1, NULL, NULL),
(10006809, 1, 1, 5, 1, NULL, NULL),
(10006810, 1, 1, 6, 1, NULL, NULL);

EXEC [dbo].[TVP_DealerOfferPrice] @OfferPriceTVP;
GO

SQL Server shows me that only 1 row gets affected and indeed last value gets only into my table. Any idea why?

You're selecting the `TOP (1)` row from the TVP - and then inserting that row. So of course, only that one single row will be inserted ..... — marc_s, Jan 12 '15 at 13:35

dario · Accepted Answer · 2015-01-12T15:36:37.183

0

Basically, since your variable cannot hold more than one value at the same time, with this statement:

SELECT @OfferId = a.OfferId
      ,@CountryId = a.CountryId
      ,@VatRateId = a.VatRateId
      ,@SalePrice = a.SalePrice
      ,@SaleFromDate = a.SaleFromDate
      ,@SaleToDate = a.SaleToDate
      ,@DefaultPrice = a.DefaultPrice
FROM @OfferPriceTVP a;

you are holding only one record of your input table.

I guess that you are trying to merge the input table with the OfferPrice table. So, you better use the MERGE statement. Here is an example:

MERGE OfferPrice AS TARGET
USING (SELECT VatRateId
             ,CASE WHEN Price IS NULL AND SalePrice IS NOT NULL AND GETUTCDATE() >= SaleFromDate AND GETUTCDATE() < SaleFromDate THEN SalePrice ELSE DefaultPrice END AS Price
             -- And so on and so forth
       FROM @OfferPriceTVP) AS SOURCE
    ON TARGET.OfferId = SOURCE.OfferId
WHEN MATCHED THEN
    UPDATE SET VatRateId = SOURCE.VatRateId
              ,Price     = SOURCE.Price
              -- And so on and so forth
WHEN NOT MATCHED THEN
    INSERT (OfferId, CountryId) -- And so on and so forth
    VALUES (SOURCE.OfferId, SOURCE.CountryId) -- And so on and so forth

More informations here:

MERGE (Transact-SQL)

CASE (Transact-SQL)

edited Jan 12 '15 at 15:36

answered Jan 12 '15 at 13:06

dario

5,149
12
28
32

questioner is trying to insert all values of table variable into [dbo].[OfferPrice] using stored procedure. does we can add exec in Insert statement. In 2010 version it is throwing error. – koushik veldanda Jan 12 '15 at 13:11
@koushikveldanda I know. His approach is wrong. That's the way, by using the input table in all the queries inside the stored procedure. There is no 2010 version of SQL Server man!! – dario Jan 12 '15 at 13:13
sorry I mean SQL Server 2008 R2 which was released in 2010 – koushik veldanda Jan 12 '15 at 13:30
Do you have idea how can use SELECT in IF statement. When I am trying GETUTCDATE() >= (Select SaleFromDate FROM @OfferPriceTVP) it gives me an error that more than one value is returned? the rest is fixed – kali Jan 12 '15 at 13:56
@MateuszSzewczyk wrong approach. What you have to do is use CASE and evaluate that condition for every row of your input table. – dario Jan 12 '15 at 13:59
@king.code can you mayby give me a snippet of syntax I should use because I trying different implementations and cant make it work – kali Jan 12 '15 at 15:20
1

Use caution with MERGE, there are a number issues with that statement. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – Sean Lange Jan 12 '15 at 19:35
@SeanLange I successfully used MERGE without trouble for a while. I didn't know about that and I'll have a look at it, thank you. – dario Jan 12 '15 at 19:42
It works fine in a number of situations but there are times when it just doesn't work quite right. Not a huge deal if you know the shortcomings. Hopefully MS will fix it in the future. – Sean Lange Jan 12 '15 at 19:44

Procedure inserts only one record

1 Answers1