Select a query using a variable not running in mssql

Question

Query selection does not work when the use variable in MSSQL2014 My schema: -

    CREATE TABLE product 
    (idproduct int, name varchar(50), description varchar(50), tax decimal(18,0))


INSERT INTO product
    (idproduct, name, description,tax)
VALUES
    (1, 'abc', 'This is abc',10),
    (2, 'xyz', 'This is xyz',20),
    (3, 'pqr', 'This is pqr',15)


CREATE TABLE product_storage 
    (idstorage int,idproduct int,added datetime, quantity int, price decimal(18,0))


INSERT INTO product_storage 
    (idstorage,idproduct, added, quantity,price)
VALUES
    (1, 1, 2010-01-01,0,10.0),
    (2, 1, 2010-01-02,0,11.0),
    (3, 1, 2010-01-03,10,12.0),
    (4, 2, 2010-01-04,0,12.0),
    (5, 2, 2010-01-05,10,11.0),
(6, 2, 2010-01-06,10,13.0),
(7, 3, 2010-01-07,10,14.0),
(8, 3, 2010-01-07,10,16.0),
(9, 3, 2010-01-09,10,13.0)

and I execute the command below: -

declare @price1 varchar(10)


SELECT p.idproduct, p.name, p.tax,
[@price1]=(SELECT top 1 s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC),
 (@price1 * (1 + tax/100)) AS [price_with_tax]
FROM product p

;

This does not work in MSSQL, please help me. for detailed control http://sqlfiddle.com/#!6/91ec2/296

And my query works in MYSQL Check the details: - http://sqlfiddle.com/#!9/a71b8/1

Answer 1

Try the following:

Explanation: You cannot use the variable on the fly, but you can do line counting in APPLY ...

SELECT p.idproduct, p.name, p.tax,
       Price.price1,
       (price1 * (1 + tax/100)) AS [price_with_tax]
FROM product p
CROSS APPLY (SELECT top 1 s.price
             FROM product_storage s
             WHERE s.idproduct=p.idproduct AND s.quantity > 0
             ORDER BY s.added ASC) AS Price(price1)

;

EDIT: your script uses a bad letter date format, try the following:

INSERT INTO product_storage 
    (idstorage,idproduct, added, quantity,price)
VALUES
    (1, 1, '20100101',0,10.0),
    (2, 1, '20100102',0,11.0),
    (3, 1, '20100103',10,12.0),
    (4, 2, '20100104',0,12.0),
    (5, 2, '20100105',10,11.0),
    (6, 2, '20100106',10,13.0),
    (7, 3, '20100107',10,14.0),
    (8, 3, '20100108',10,16.0),
    (9, 3, '20100109',10,13.0)

Answer 2

SELECT 
    p.idproduct
    , p.name
    , p.tax
    , (t1.price * (1 + tax/100)) AS [price_with_tax]
FROM product p
inner join 
(
    SELECT ROW_NUMBER() over (PARTITION by s.idproduct order by s.added ASC) as linha, s.idproduct, s.price 
    FROM product_storage s
    WHERE s.quantity > 0    
) as t1
    on t1.idproduct = p.idproduct and t1.linha = 1

Answer 3

Here is the correct schema for SQL Server, and the query runs fine, like Shnugo Replied.

 VALUES
        (1, 1, convert(datetime,'2010-01-01'),0,10.0),
        (2, 1, convert(datetime,'2010-01-02'),0,11.0),
        (3, 1, convert(datetime,'2010-01-03'),10,12.0),
        (4, 2, convert(datetime,'2010-01-04'),0,12.0),
        (5, 2, convert(datetime,'2010-01-05'),10,11.0),
        (6, 2, convert(datetime,'2010-01-06'),10,13.0),
        (7, 3, convert(datetime,'2010-01-07'),10,14.0),
        (8, 3, convert(datetime,'2010-01-07'),10,16.0),
        (9, 3, convert(datetime,'2010-01-09'),10,13.0)