SQL Server base64编码存储功能 [英] SQL Server base64 encoding stored function
本文介绍了SQL Server base64编码存储功能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一个简单的存储函数,用于进一步的SQL Server base64编码,但是对于我传递的每个字符串,它都返回null。我认为sql:参数不能使用input函数参数,但不知道如何避免它。
这是我的代码:
ALTER FUNCTION [dbo]。 [usf_base64_encode]
(@value nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN cast(''as xml) xs:base64Binary(sql:variable(@ value))','varchar(max)')
END
此代码片段可以正常工作,但它声明了在函数中无法做到的局部变量。
declare @ source varbinary(max),@encoded varchar(max),@decoded varbinary(max)
set @source = convert(varbinary(max),'Hello Base64')
set @encoded = 'as xml).value('xs:base64Binary(sql:variable(@ source))','varchar(max)')
set @decoded = cast(''as xml) xs:base64Binary(sql:variable(@ encoded))','varbinary(max)')
选择
convert(varchar(max),@source)as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoding _binary,
convert(varchar(max),@decoded)as decoded_varchar
解决方案嗯,我呃相当愚蠢的。这个代码的作用是:
ALTER FUNCTION [dbo]。[usf_base64_encode]
(
@value varchar最大)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @source varbinary(max)= convert(varbinary(max),@value)
RETURN cast(''as xml).value('xs:base64Binary(sql:variable(@ source))','varchar(max)')
END
I need a simple stored function for further SQL Server base64 encoding, but it returns null for every string I pass. I think that sql:parameter is not working with input function parameter, but don't know how to avoid it.
This is my code:
ALTER FUNCTION [dbo].[usf_base64_encode]
(@value nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
RETURN cast('' as xml).value('xs:base64Binary(sql:variable("@value"))', 'varchar(max)')
END
This code snippet works, but it declares local variables that's impossible to do in function.
declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)
set @source = convert(varbinary(max), 'Hello Base64')
set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')
select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar
解决方案
Well, I defenitly am pretty dumb. This code works:
ALTER FUNCTION [dbo].[usf_base64_encode]
(
@value varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @source varbinary(max) = convert(varbinary(max), @value)
RETURN cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
END
这篇关于SQL Server base64编码存储功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文