CREATE FUNCTION dbo.get_prxi(@id int, @nosafe nvarchar(50), @code nvarchar(50), @pr_num1 nvarchar(50), @pr_num2 nvarchar(50), @pr_lang nvarchar(50))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @r nvarchar(4000)
DECLARE @tj nvarchar(500)
set @tj=''
if @nosafe='0' or @nosafe='1'
begin
if @nosafe='0'
begin
set @tj=@tj + ' and pr_num>pr_numsafe'
end
else
begin
set @tj=@tj + ' and pr_num<=pr_numsafe'
end
end
if @pr_num1<>''
begin
set @tj=@tj + ' and pr_num>=' +@pr_num1
end
if @pr_num2<>''
begin
set @tj=@tj + ' and pr_num<' +@pr_num2
end
if @code<>''
begin
set @tj=@tj + ' and pr_code like ''%'+@code+'%'''
end
select @r = ISNULL(@r + '','') + '<pr><pr_code>'+ pr_code + '</pr_code><pr_num>'+rtrim(pr_num)+'</pr_num><pr_numsafe>'+rtrim(pr_numsafe)+'</pr_numsafe></pr>' from tbao_prxi where pr_id=@id +@tj
RETURN @r
END只要加入@nosafe、@pr_num1、@pr_num2、@code任一条件后,都会报此类错误:将 nvarchar 值 ' and pr_num>=4' 转换为数据类型为 int 的列时发生语法错误。头痛这种带条件的自定义函数要怎么写呢
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @r nvarchar(4000)
DECLARE @tj nvarchar(500)
set @tj=''
if @nosafe='0' or @nosafe='1'
begin
if @nosafe='0'
begin
set @tj=@tj + ' and pr_num>pr_numsafe'
end
else
begin
set @tj=@tj + ' and pr_num<=pr_numsafe'
end
end
if @pr_num1<>''
begin
set @tj=@tj + ' and pr_num>=' +@pr_num1
end
if @pr_num2<>''
begin
set @tj=@tj + ' and pr_num<' +@pr_num2
end
if @code<>''
begin
set @tj=@tj + ' and pr_code like ''%'+@code+'%'''
end
select @r = ISNULL(@r + '','') + '<pr><pr_code>'+ pr_code + '</pr_code><pr_num>'+rtrim(pr_num)+'</pr_num><pr_numsafe>'+rtrim(pr_numsafe)+'</pr_numsafe></pr>' from tbao_prxi where pr_id=@id +@tj
RETURN @r
END只要加入@nosafe、@pr_num1、@pr_num2、@code任一条件后,都会报此类错误:将 nvarchar 值 ' and pr_num>=4' 转换为数据类型为 int 的列时发生语法错误。头痛这种带条件的自定义函数要怎么写呢
-->
pr_id=ltrim(@id) +@tj
select @r = ISNULL(@r + '','') + '<pr><pr_code>'+ pr_code + '</pr_code><pr_num>'+rtrim(pr_num)+'</pr_num><pr_numsafe>'+rtrim(pr_numsafe)+'</pr_numsafe></pr>' from tbao_prxi where pr_id=@id +@tj应该改成用动态SQL,而函数中不允许有动态SQL,所以最终建议是改成用存储过程.