我将楼上的语句放进SQL的自定义函数如下: 结果每次执行都报错.CREATE FUNCTION [dbo].[Str16_to_chr8] (@lc_str16 char(8)) RETURNS char(8) AS BEGIN declare @sql nvarchar(100), @binary varbinary(8), @return char(8) -------------------------------------------------------------------------------------------------------- set @sql=N'set @binary=0x'+@lc_str16 exec sp_executesql @sql,N'@binary varbinary(8) out',@binary out -------------------------------------------------------------------------------------------------------- set @return = cast(replicate('0',(8-len(cast(cast(@binary as int) as char(8)))))+cast(cast(@binary as int) as char(8)) as char(8)) -------------------------------------------------------------------------------------------------------- return @return end 执行语句如下: select [dbo].Str16_to_chr8('00EDDAFB')错误提示如下: 服务器: 消息 557,级别 16,状态 2,过程 Str16_to_chr8,行 9 只有函数和扩展存储过程才能从函数内部执行。请问应该如何改?
--函数参考下面改改declare @Str varchar(10) set @Str='00EDDAFB'/* if patindex('%[^0-9a-fA-F]%',@Str)>0 return (null) */declare @Position int,@4bit int,@Return varbinary(8) select @Position=len(@Str),@4bit=0,@Return=0 while @Position>0 begin set @Return=@Return+ ( case isnumeric(substring(@Str,@Position,1)) when 0 then ascii(upper(substring(@Str,@Position,1)))-55 else substring(@Str,@Position,1) end )*power(convert(bigint,16),@4bit) select @Position=@Position-1,@4bit=@4bit+1 endselect @Return
建议在前台程序里做.
set @sql=N'set @binary=0x'+@strexec sp_executesql @sql,N'@binary varbinary(8) out',@binary outselect @binary
RETURNS char(8)
AS
BEGIN
declare @sql nvarchar(100), @binary varbinary(8), @return char(8)
--------------------------------------------------------------------------------------------------------
set @sql=N'set @binary=0x'+@lc_str16
exec sp_executesql @sql,N'@binary varbinary(8) out',@binary out
--------------------------------------------------------------------------------------------------------
set @return = cast(replicate('0',(8-len(cast(cast(@binary as int) as char(8)))))+cast(cast(@binary as int) as char(8)) as char(8))
--------------------------------------------------------------------------------------------------------
return @return
end
执行语句如下:
select [dbo].Str16_to_chr8('00EDDAFB')错误提示如下:
服务器: 消息 557,级别 16,状态 2,过程 Str16_to_chr8,行 9
只有函数和扩展存储过程才能从函数内部执行。请问应该如何改?
set @Str='00EDDAFB'/*
if patindex('%[^0-9a-fA-F]%',@Str)>0
return (null)
*/declare @Position int,@4bit int,@Return varbinary(8)
select @Position=len(@Str),@4bit=0,@Return=0
while @Position>0
begin
set @Return=@Return+
(
case isnumeric(substring(@Str,@Position,1))
when 0 then ascii(upper(substring(@Str,@Position,1)))-55
else substring(@Str,@Position,1)
end
)*power(convert(bigint,16),@4bit)
select @Position=@Position-1,@4bit=@4bit+1
endselect @Return