大家帮忙看一下,我在查询分析器中执行这个函数时,为什么提示“游标未打开”呢?ALTER FUNCTION getInvoiceNoOfSettle (@OutputBillID INT)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @InvoiceNoString VARCHAR
DECLARE @InvoiceNo VARCHAR
DECLARE TempCursor CURSOR FOR SELECT sa.FInvoiceNo FROM Bill_SaleOutput_Detail b LEFT OUTER JOIN Bill_SaleOutputSettle_Detail sb ON @OutputBillID=sb.FOutputBillID
LEFT OUTER JOIN Bill_SaleOutputSettle_Base sa ON sa.FBillID=sb.FBillID
LEFT OUTER JOIN Bill_SaleOutput_Base a ON a.FBillID=b.FBillID
WHERE a.FCheck1=1 AND sa.FCheck1=1
FETCH NEXT FROM TempCursor INTO @InvoiceNo
OPEN TempCursor
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @InvoiceNoString=@InvoiceNoString+','+@InvoiceNo
FETCH NEXT FROM TempCursor INTO @InvoiceNo
END
CLOSE TempCursor
DEALLOCATE TempCursor
RETURN @InvoiceNoString
END
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @InvoiceNoString VARCHAR
DECLARE @InvoiceNo VARCHAR
DECLARE TempCursor CURSOR FOR SELECT sa.FInvoiceNo FROM Bill_SaleOutput_Detail b LEFT OUTER JOIN Bill_SaleOutputSettle_Detail sb ON @OutputBillID=sb.FOutputBillID
LEFT OUTER JOIN Bill_SaleOutputSettle_Base sa ON sa.FBillID=sb.FBillID
LEFT OUTER JOIN Bill_SaleOutput_Base a ON a.FBillID=b.FBillID
WHERE a.FCheck1=1 AND sa.FCheck1=1
FETCH NEXT FROM TempCursor INTO @InvoiceNo
OPEN TempCursor
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @InvoiceNoString=@InvoiceNoString+','+@InvoiceNo
FETCH NEXT FROM TempCursor INTO @InvoiceNo
END
CLOSE TempCursor
DEALLOCATE TempCursor
RETURN @InvoiceNoString
END
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @InvoiceNoString VARCHAR
DECLARE @InvoiceNo VARCHAR
DECLARE TempCursor CURSOR FOR SELECT sa.FInvoiceNo FROM Bill_SaleOutput_Detail b LEFT OUTER JOIN Bill_SaleOutputSettle_Detail sb ON @OutputBillID=sb.FOutputBillID
LEFT OUTER JOIN Bill_SaleOutputSettle_Base sa ON sa.FBillID=sb.FBillID
LEFT OUTER JOIN Bill_SaleOutput_Base a ON a.FBillID=b.FBillID
WHERE a.FCheck1=1 AND sa.FCheck1=1
OPEN TempCursor
FETCH NEXT FROM TempCursor INTO @InvoiceNo
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @InvoiceNoString=@InvoiceNoString+','+@InvoiceNo
FETCH NEXT FROM TempCursor INTO @InvoiceNo
END
CLOSE TempCursor
DEALLOCATE TempCursor
RETURN @InvoiceNoString
END
OPEN TempCursor
---FETCH NEXT FROM TempCursor INTO @InvoiceNo
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM TempCursor INTO @InvoiceNo
RETURNS VARCHAR(200)
begin
declare @InvoiceNo varchar(2000)
select @InvoiceNo=isnull(@InvoiceNo+',','')+sa.FInvoiceNo
FROM Bill_SaleOutput_Detail b
LEFT OUTER JOIN Bill_SaleOutputSettle_Detail sb ON @OutputBillID=sb.FOutputBillID
LEFT OUTER JOIN Bill_SaleOutputSettle_Base sa ON sa.FBillID=sb.FBillID
LEFT OUTER JOIN Bill_SaleOutput_Base a ON a.FBillID=b.FBillID
WHERE a.FCheck1=1 AND sa.FCheck1=1RETURN @InvoiceNo
end为什么用游标这样可以么。
游标没有及时打开啊,我真的是傻傻的还要谢谢东升哥,永生哥,我试了下,真的不用游标也可以哦~奇怪。
我只知道我原来听说过的是用select给变量赋值时,当返回多个值时,只将最后一个赋给它啊
....
OPEN TempCursor
FETCH NEXT FROM TempCursor INTO @InvoiceNo
...
OPEN TempCursor
select @InvoiceNo=isnull(@InvoiceNo+',','')+sa.FInvoiceNo 这句话是把sa.FInvoiceNo下的数据值连接起来,并以逗号隔开!你可以看一下效果的...
OPEN TempCursor
open游标后才能
fetch游标