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)
精彩评论