sql-server - 在 sql server 中为 UDF 创建正文

Question

我正在尝试function在我的sql server数据库中创建一个

我写的：

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.

那么请我如何在我的函数中使用这个身体？并提前感谢

标签： sql-servertsqluser-defined-functions

Answer 1

做一个内联表函数而不是一个多语句（用一个光标引导），它会快得多：

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