CODEWITHSUNDEEP
sql-servervariablesselect
Gautam Chawla
Gautam Chawla

Reputation: 65

Select query using variable not running in mssql

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

Upvotes: 4

Views: 345

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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)

Upvotes: 1

Gautam Chawla
Gautam Chawla

Reputation: 65

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)

Upvotes: 0

Thiago
Thiago

Reputation: 56

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

Upvotes: 3

Related Questions