SQL Query Declaration problem

Question

I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem?

Incorrect syntax near the keyword 'Declare'

SQL Query:

create View [dbo].StokMaliyet
AS
Declare @Dolar decimal(18,3),@Euro decimal(18,3)

set @Dolar=(select Dolar from GunlukDoviz
set @Euro=(select Euro from GunlukDoviz)

 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*@Dolar)/@Euro
 When dovtip='0' then (net_maliyet)/@Euro
 end )  as Maliyet
 from ib_mlyt

Answer 1

With a CTE?

This assumes that you get one row from Dolar and Euro. If you get more then one, add a TOP 1 ..ORDER BY: your original query is wrong too then

create View [dbo].StokMaliyet
AS

WITH
    Dolar AS (select Dolar from GunlukDoviz),
    Euro AS (select Euro from GunlukDoviz)
 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*Dolar)/Euro 
 When dovtip='0' then (net_maliyet)/Euro
 end )  as Maliyet
 from ib_mlyt
     CROSS JOIN
      Dolar 
     CROSS JOIN
      Euro 
GO

Answer 2

You cannot use declare and any code manipulation in view (select statement ONLY at SQL Server of course).

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     } 

See for more details

Use stored procedure instead of view

Answer 3

You would have to change it to

create View [dbo].StokMaliyet
AS
 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*(select top (1) Dolar from GunlukDoviz))/(select top (1) Euro from GunlukDoviz)
 When dovtip='0' then (net_maliyet)/(select top (1) Euro from GunlukDoviz)
 end )  as Maliyet
 from ib_mlyt

Hope this helps