TSQL: Trouble with creating function

I am trying to create T-SQL function from Northwind to return new table, that will containt ProductID, ProductName, UnitsInStock and new column indicating if there are more UnitsInStock than function parameter.

Example: Let's have table of 2 products. First has 10 units in stock, second has 5. So function with parameter 6 should return:

1, Product1, 10, YES

2, Product2, 5, NO

Here's my non working code sofar :(

CREATE FUNCTION dbo.ProductsReorder
    (
    @minValue int
    )
RETURNS  @tabvar TABLE (int _ProductID, nvarchar _ProductName, int _UnitsInStock, nvarchar _Reorder) 
AS
    BEGIN
        INSERT INTO @tabvar 
           SELECT ProductID, ProductName, UnitsInStock, 开发者_开发技巧Reorder =
           CASE
            WHEN UnitsInStock > @minValue THEN "YES"
            ELSE "NO"
           END 
            FROM Products
        RETURN
    END

T-SQL gives me this not really helpful answer: "Column, parameter, or variable#1: Cannot find data type _ProductID". I googled but I found gazillion different issues for such a result.

I dunno if it's good to use CASE here, I have a little Oracle background and decode function was great for these issues.

---

it's an easy answer -- especially as you are from oracle

the table definition in your function is the wrong way round.

Replace:

@tabvar TABLE (int _ProductID, nvarchar _ProductName, int _UnitsInStock, nvarchar _Reorder)

with something like

@tabvar TABLE ([_ProductID] INT, [_ProductName] NVARCHAR(50), [_UnitsInStrock] INT, [_Reorder] NVARCHAR(50))

In sql server the types come after the column names

---

In your table definition, you should put the column name first, then the data type. For example

_UnitsInStock int, ...

also, the NVARCHAR data type needs a length value.

_ProductName nvarchar(20)