想将Ms_PreferredPayment表中cCashCode为指定值的行的cCheckCode字段的值计算出来,所以做了一个函数,检查语法时出现错误如下:服务器: 消息 444,级别 16,状态 2,过程 MS_PreferredCheckCode,行 20
函数中含有的 SELECT 语句无法向客户端返回数据。不知道错在哪里,请各位帮忙啊
CREATE FUNCTION MS_PreferredCheckCode(@cCashCode AS varchar(12))
RETURNS varchar
AS
BEGIN
DECLARE @cCheckCode varchar
If Not Exists (Select * from Ms_PreferredPayment Where cCashCode=@cCashCode)
Set @cCheckCode=null
Else
Set @cCheckCode=''
Begin
Declare @tmpCheckCode varchar
Declare Mycursor cursor for select cCheckCode from Ms_PreferredPayment where cCashCode=@cCashCode --为所获得的数据集指定游标
Open Mycursor --打开游标
Fetch next from Mycursor into @tmpCheckCode --开始抓数据
WHILE @@FETCH_STATUS = 0 --这时才可以检查@@fetch_status的值,=0表示成功,这里是一个循环
Set @cCheckCode=@cCheckCode+';'+@tmpCheckCode
FETCH NEXT FROM mycursor --这里的fetch语句被循环执行。
Close mycursor --关闭游标
Deallocate mycursor --删除游标
End RETURN @cCheckCode
END
GO
函数中含有的 SELECT 语句无法向客户端返回数据。不知道错在哪里,请各位帮忙啊
CREATE FUNCTION MS_PreferredCheckCode(@cCashCode AS varchar(12))
RETURNS varchar
AS
BEGIN
DECLARE @cCheckCode varchar
If Not Exists (Select * from Ms_PreferredPayment Where cCashCode=@cCashCode)
Set @cCheckCode=null
Else
Set @cCheckCode=''
Begin
Declare @tmpCheckCode varchar
Declare Mycursor cursor for select cCheckCode from Ms_PreferredPayment where cCashCode=@cCashCode --为所获得的数据集指定游标
Open Mycursor --打开游标
Fetch next from Mycursor into @tmpCheckCode --开始抓数据
WHILE @@FETCH_STATUS = 0 --这时才可以检查@@fetch_status的值,=0表示成功,这里是一个循环
Set @cCheckCode=@cCheckCode+';'+@tmpCheckCode
FETCH NEXT FROM mycursor --这里的fetch语句被循环执行。
Close mycursor --关闭游标
Deallocate mycursor --删除游标
End RETURN @cCheckCode
END
GO
WHILE @@FETCH_STATUS = 0
BEGIN
Set @cCheckCode=@cCheckCode+';'+@tmpCheckCode
Fetch next from Mycursor into @tmpCheckCode
END 2. 貌似需求是合併字串?
不需要用cursor啊搜一下,很多例子的
RETURNS varchar
AS
begin
DECLARE @cCheckCode varchar
select @cCheckCode=isnull(@cCheckCode,'')+';'+cCheckCode from Ms_PreferredPayment where cCashCode=@cCashCode
if len(@cCheckCode)>0
set @cCheckCode=stuff(@cCheckCode,1,1,'')return @cCheckCodeend
FETCH NEXT FROM mycursor --这里的fetch语句被循环执行。====>
Fetch next from Mycursor into @tmpCheckCode
RETURNS varchar(400)
AS
BEGIN
DECLARE @cCheckCode varchar(400)
Select @cCheckCode=isnull(@cCheckCode,'')+';'+cCheckCode from Ms_PreferredPayment where cCashCode=@cCashCode
SET @cCheckCode=CASE WHEN @cCheckCode IS NOT NULL THEN STUFF(@cCheckCode,1,1,'') ELSE @cCheckCode END
RETURN @cCheckCode
END
GO
3楼的逻辑好像全了
用5楼的更简单
CREATE FUNCTION MS_PreferredCheckCode(@cCashCode AS varchar(12))
RETURNS varchar(100)
AS
BEGIN
DECLARE @cCheckCode varchar(100)
If Not Exists (Select 1 from Ms_PreferredPayment Where cCashCode=@cCashCode)
Set @cCheckCode=null
Else
Begin
Set @cCheckCode=''
Declare @tmpCheckCode varchar(100)
Declare Mycursor cursor for
select cCheckCode from Ms_PreferredPayment where cCashCode=@cCashCode
Open Mycursor
Fetch next from Mycursor into @tmpCheckCode
WHILE @@FETCH_STATUS = 0
begin
Set @cCheckCode=@cCheckCode+';'+@tmpCheckCode
FETCH NEXT FROM mycursor INTO @tmpCheckCode
end
Close mycursor
Deallocate mycursor
End
RETURN @cCheckCode
END
AS
BEGIN
DECLARE @cCheckCode varchar(400)
Select @cCheckCode=isnull(@cCheckCode+';','')+cCheckCode
from Ms_PreferredPayment
where cCashCode=@cCashCode
RETURN @cCheckCode
END
GO