Select query using variable not running in mssql

Question

Select query is not working when use variable in MSSQL2014 My Schema is :-

    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 am executing below command:-

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 is not working in MSSQL, Please Help me out. for detail check http://sqlfiddle.com/#!6/91ec2/296

And My query is working in MYSQL Check for detail :- http://sqlfiddle.com/#!9/a71b8/1

A good example for a good question, thanks for the Fiddles – Shnugo Dec 16 '15 at 11:53 — Shnugo, Dec 16 '15 at 11:53

score 3 · Answer 1 · answered Dec 16 '15 at 11:39

3

Try this query

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

answered Dec 16 '15 at 11:39

Thiago

56
4

This query is also not giving the correct result of the last row, Please check with MYSQL Query – Gautam Chawla Dec 16 '15 at 11:45
1

The last row (product 3) has two identical dates (2010-01-07). You need to add another condition in the query (order by) or add time with the date. e.g. 2010-01-07 10:00:00 and 2010-01-07 15:32:00 – Thiago Dec 16 '15 at 11:53
But with the same schema it works in MYSQL Check this :- http://sqlfiddle.com/#!9/a71b8/1 – Gautam Chawla Dec 16 '15 at 11:59
I updated last row inserted in the product_storage with same date (2010-01-07) and the result was different. check this http://sqlfiddle.com/#!9/964730/1 – Thiago Dec 16 '15 at 12:29
Hi, there was a problem with the literally inserted dates in the Fiddle , think there was sort of a subtraction of int values rather than parsing a date... – Shnugo Dec 16 '15 at 12:31

Shnugo · Accepted Answer · 2015-12-16T13:52:27.513

1

Try it like this:

Explanantion: You cannot use a variable "on the fly", but you can do row-by-row calculation in an 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 Fiddle uses a bad literal date format, try this:

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)

edited Dec 16 '15 at 13:52

answered Dec 16 '15 at 11:53

Shnugo

66,100
9
53
114

this query is also not giving the correct result of the last row, Please compare with MYSQL http://sqlfiddle.com/#!9/a71b8/1 – Gautam Chawla Dec 16 '15 at 12:00
1

AS TFroes pointed out, the reason is your thinking of sorting. SQL Server keeps no implicit row order which you could rely on. If there are more than one rows with the same value you are sorting with, it will be **totally random** which occurs first... Try both queries with unique dates and/or include the time. – Shnugo Dec 16 '15 at 12:03
Okay, But i have change the identical values, then also it shows the same result Check this :-http://sqlfiddle.com/#!6/c896a/2 – Gautam Chawla Dec 16 '15 at 12:05
@GautamChawla, Yes this was a bit odd. The reason is the way you insert the dates. Just try in your fiddle this line `select * from product_storage order by added` and explore the added column. For literal date usage read this: http://stackoverflow.com/a/34275965/5089204 – Shnugo Dec 16 '15 at 12:26
Hi, Thanks this works fine when i convert date time. Thanks for your Help :) – Gautam Chawla Dec 16 '15 at 12:49
As pointed in @TFroe's answer, your "order" clause is ordering by date. In your fiddle, there are two equal dates for product 3. SQL Server will order them in a random way. This can produce unpredictable results.. be careful. – jpgrassi Dec 16 '15 at 13:50
@jpgrassi just some comments above you'll find the words **totally random** :-) – Shnugo Dec 16 '15 at 13:51

Gautam Chawla · Answer 3 · 2015-12-16T12:59:41.683

Here is the correct schema for SQL Server and query runs perfect as 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)

Select query using variable not running in mssql

3 Answers3