我正在尝试创建一个sql函数来测试参数是以某个术语开头还是包含该术语,但不是以它开头.
基本上,如果参数以term开头,则函数返回0.否则返回1.
这是我所拥有的函数的骨骼,我正在尝试从我发现的另一个函数中进行调整：
CREATE FUNCTION [dbo].[fnGetRelevance]
(
@fieldName nvarchar(50),@searchTerm nvarchar(50)
)
RETURNS @value int -- this is showing an error,it seems to expect table but all I want is an int
(
-- does this need to be here? If so,what should it be?
)
AS
BEGIN
declare @field TABLE(Data nvarchar(50))
insert into @field
select Data from @fieldName
if (Data like @searchTerm + '%') -- starts with
begin
return 0
end
else if (Data like '%' + @searchTerm + '%' and Data not like @searchTerm + '%') -- contains,but doesn't start with
begin
return 1
end
END
GO
解决方法
您不为返回值提供变量名称,只提供其类型,并且不需要parens;
CREATE FUNCTION [dbo].[fnGetRelevance]
(
@fieldName nvarchar(50),@searchTerm nvarchar(50)
)
RETURNS int
AS
....
也;
select Data from @fieldName
不起作用,你需要dynamic SQL来从一个变量中名称的对象中进行选择.
