在sql server中为UDF创建主体

Question

我想在我的sql server数据库中创建一个function

我写的:

CREATE FUNCTION CommandesAsTable ()
RETURNS TABLE
AS  
DECLARE MyC CURSOR FOR SELECT ID,QTE,PRU FROM Commande
DECLARE @ID VARCHAR(50), @QTE INT , @PRU MONEY, @Total MONEY
DECLARE @R AS TABLE (ID VARCHAR(50), QTE INT , PRU MONEY, Total MONEY)
OPEN MyC
FETCH MyC INTO @ID,@QTE,@PRU
WHILE @@fetch_status =0
BEGIN 
SET @total = @QTE*@PRU
INSERT INTO @R VALUES(@id,@QTE,@PRU,@Total)
FETCH MyC INTO @ID,@QTE,@PRU
END 
CLOSE myc
DEALLOCATE myc       
RETURN @R

但是我得到了这个错误:

Msg 156, Level 15, State 1, Procedure CommandesAsTable, Line 5 [Batch Start Line 30]
Incorrect syntax near the keyword 'DECLARE'.
Msg 178, Level 15, State 1, Procedure CommandesAsTable, Line 25 [Batch Start Line 30]
A RETURN statement with a return value cannot be used in this context.

那么我如何在函数中使用这个主体呢?提前感谢

Answer 1

使用内联表函数而不是多语句(引导游标)，这样会快得多:

CREATE FUNCTION CommandesAsTable()
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
(
SELECT ID,QTE,PRU, Total = QTE * PRU
FROM Commande
)
GO