你能解释一下这个功能的输出吗? [英] Could you please explain the output of the function
问题描述
CREATE FUNCTION [dbo]。[udf_GenerateWhereQuery]
(@logic char(10),@ Field varchar(250),@ Operator varchar(100),@ Value Nvarchar(max),@ DataType varchar(100),@ RowNum int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @WhereQuery NVarchar(MAX),@ SQLOperator NVARCHAR(MAX)
SET @SQLOperator = CASE
WHEN @DataType ='String'
THEN CASE
WHEN @Operator ='startswith'
那么@Field +'LIKE'+'N'''+ @Value +'%'+''''
WHEN @Operator ='endswith'
那么@Field +'LIKE'+'N'''+'%'+ @Value +''''>
WHEN @Operator ='eq'
那么@Field +'='+'N'''+ @Value +''''
WHEN @Operator ='neq'
那么'ISNULL('+ @ Field +','''')'+'<>'+'N'''+ @ Value +'' ''>
WHEN @ Operator ='contains'
那么@Field +'LIKE'+'N'''+'%'+ @ Value +'%'+''''
WHEN @Operator ='不包含'或@Operator ='不能包含'
那么'ISNULL('+ @Field +','''')'+'不喜欢'+'N'''+'%'+ @ Value +'%'+''''
END
WHEN @DataType ='Numeric'
那么情况
WHEN @Operator ='gte'
那么@Field +'> ='+ @Value
WHEN @Operator ='lte'
THEN @Field +'< ='+ @Value
WHEN @Operator ='gt'
THEN @Field +'> '+ @Value
WHEN @Operator ='lt'
THEN @Field +'< '+ @Value
WHEN @Operator ='eq'
那么@Field +'='+ @Value
WHEN @Operator =' neq'
THEN @Field +'<>'+ @Value
END
WHEN @DataType IN('DateTime')
那么情况
WHEN @Operator ='gte'
那么@Field +'> ='+''''+ @Value + ''''
WHEN @Operator ='lte'
那么@Field +'< ='+''''+ @Value +''''
WHEN @Operator ='gt'
THEN @Field +'> '+''''+ @Value +''''
WHEN @Operator ='lt'
THEN @Field +'< '+''''+ @Value +''''
WHEN @Operator ='eq'
THEN @Field +'='+''''+ @Value +''''
WHEN @Operator ='neq'
那么@Field +'<>'+''''+ @Value +' '''
结束
当@DataType IN('日期')
那么情况
当@Operator ='gte'
那么'CONVERT(varchar(10),'+ @ Field +',120)'+'> ='+''''+ CONVERT(varchar(10),@价值,120)+''''
WHEN @Operator ='lte'
那么'CONVERT(varchar(10),'+ @ Field +',120)' +'< ='+''''+ CONVERT(varchar(10),@ Value,120)+''''
WHEN @Operator ='gt'
THEN'CONVERT(varchar(10),'+ @ Field +',120)'+'> '+''''+ CONVERT(varchar(10),@ Value,120)+''''
WHEN @Operator ='lt'
THEN'CONVERT (varchar(10),'+ @ Field +',120)'+'> '+''''+ CONVERT(varchar(10),@ Value,120)+''''
WHEN @Operator ='eq'
THEN'CONVERT (varchar(10),'+ @ Field +',120)'+'='+''''+ CONVERT(varchar(10),@ Value,120)+''''
当@Operator ='neq'
那么'CONVERT(varchar(10),'+ @ Field +',120)'+'<> '+''''+ CONVERT(varchar(10),@ Value,120)+''''
END
WHEN @DataType ='Boolean'
那么情况
当@Operator ='eq'
然后@Field +'='+''''+ @Value +''' '
结束
结束
SET @ WhereQuery =
CASE
WHEN @ logic =''THEN'('+ @ SQLOperator +')'
WHEN @logic in('and','或')AND @ RowNum = 2 THEN''+ @ logic +''+ @ SQLOperator +')'
WHEN @logic in('和','或')和@ RowNum = 1那么'('+ @ SQLOperator
END
返回@WhereQuery
结束
我尝试过:
试图理解函数和输出的逻辑。
这很简单 - 而且毫无意义,真的。
您传递基于文本的参数并将其转换为有效的SQL WHERE条件作为字符串。
所以你使用它:
[dbo]。[udf_GenerateWhereQuery](' AND',' nameOfColumn',' gte',' 666',' numeric' )
它返回
' AND nameOfColumn> = 666'
哪个会我宁愿阅读我的代码?不是函数调用...
这样做的唯一好处就是它可以从数据库获取值并生成可以执行的SQL - 但这样做很危险,因为它让你对SQL注入开放。 / BLOCKQUOTE>
CREATE FUNCTION [dbo].[udf_GenerateWhereQuery]
(@logic char(10), @Field varchar(250),@Operator varchar(100), @Value Nvarchar(max), @DataType varchar(100) ,@RowNum int)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @WhereQuery NVarchar(MAX),@SQLOperator NVARCHAR(MAX)
SET @SQLOperator = CASE
WHEN @DataType = 'String'
THEN CASE
WHEN @Operator = 'startswith'
THEN @Field + ' LIKE ' + 'N''' + @Value + '%' + ''''
WHEN @Operator = 'endswith'
THEN @Field + ' LIKE ' + 'N''' + '%' + @Value +''''
WHEN @Operator = 'eq'
THEN @Field + '=' + 'N''' + @Value + ''''
WHEN @Operator = 'neq'
THEN 'ISNULL(' + @Field + ','''')' + '<>' + 'N''' + @Value + ''''
WHEN @Operator='contains'
THEN @Field + ' LIKE ' + 'N'''+'%'+@Value + '%' + ''''
WHEN @Operator ='does not contain' OR @Operator ='doesnotcontain'
THEN 'ISNULL(' + @Field + ','''')' + ' NOT LIKE ' + 'N'''+'%'+@Value + '%' + ''''
END
WHEN @DataType = 'Numeric'
THEN CASE
WHEN @Operator = 'gte'
THEN @Field + ' >= ' + @Value
WHEN @Operator = 'lte'
THEN @Field + ' <= ' + @Value
WHEN @Operator = 'gt'
THEN @Field + ' > ' + @Value
WHEN @Operator = 'lt'
THEN @Field + ' < ' + @Value
WHEN @Operator = 'eq'
THEN @Field + '=' + @Value
WHEN @Operator = 'neq'
THEN @Field + '<>' + @Value
END
WHEN @DataType IN ('DateTime')
THEN CASE
WHEN @Operator = 'gte'
THEN @Field + ' >= ' +''''+ @Value +''''
WHEN @Operator = 'lte'
THEN @Field + ' <= ' +''''+ @Value +''''
WHEN @Operator = 'gt'
THEN @Field + ' > ' +''''+ @Value +''''
WHEN @Operator = 'lt'
THEN @Field + ' < ' +''''+ @Value +''''
WHEN @Operator = 'eq'
THEN @Field + '=' +''''+ @Value +''''
WHEN @Operator = 'neq'
THEN @Field + '<>' +''''+ @Value +''''
END
WHEN @DataType IN ('Date')
THEN CASE
WHEN @Operator = 'gte'
THEN 'CONVERT(varchar(10),'+@Field+',120)' + ' >= ' +''''+ CONVERT(varchar(10),@Value,120) +''''
WHEN @Operator = 'lte'
THEN 'CONVERT(varchar(10),'+@Field+',120)' + ' <= ' +''''+ CONVERT(varchar(10),@Value,120) +''''
WHEN @Operator = 'gt'
THEN 'CONVERT(varchar(10),'+@Field+',120)' + ' > ' +''''+ CONVERT(varchar(10),@Value,120) +''''
WHEN @Operator = 'lt'
THEN 'CONVERT(varchar(10),'+@Field+',120)' + ' > ' +''''+ CONVERT(varchar(10),@Value,120) +''''
WHEN @Operator = 'eq'
THEN 'CONVERT(varchar(10),'+@Field+',120)' + ' = ' +''''+ CONVERT(varchar(10),@Value,120) +''''
WHEN @Operator = 'neq'
THEN 'CONVERT(varchar(10),'+@Field+',120)' + ' <> ' +''''+ CONVERT(varchar(10),@Value,120) +''''
END
WHEN @DataType = 'Boolean'
THEN CASE
WHEN @Operator = 'eq'
THEN @Field + '=' +''''+ @Value +''''
END
END
SET @WhereQuery=
CASE
WHEN @logic = '' THEN '('+@SQLOperator+')'
WHEN @logic in('and','or') AND @RowNum=2 THEN ' '+@logic+ ' '+@SQLOperator+')'
WHEN @logic in('and','or') AND @RowNum=1 THEN '('+@SQLOperator
END
RETURN @WhereQuery
END
What I have tried:
Trying to understand the logic of the function and output as well.解决方案It's pretty simple - and rather pointless, really.
You pass it text based parameters and it converts them to valid SQL WHERE condition as a string.
So you use it:
[dbo].[udf_GenerateWhereQuery]('AND', 'nameOfColumn', 'gte', '666', 'numeric')
And it returns
'AND nameOfColumn >= 666'
Which would I rather read in my code? Not the function call...
The only advantage of this is that it can take values from a DB and generate SQL that can be executed - but that's dangerous as it leaves you open to SQL Injection.
这篇关于你能解释一下这个功能的输出吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!