34,571
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class CheckExpression
{
[SqlFunction(IsDeterministic=true, IsPrecise=true,DataAccess=DataAccessKind.Read)]
public static int ChkExp (String exp, int param)
{
String expr = "select (case when " + exp + " then 1 else 0 end)";
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(expr, conn);
cmd.Parameters.AddWithValue("@p", param);
return (int) cmd.ExecuteScalar();
}
}
}
use master
go
exec sp_configure 'show advanced options',1;
go
reconfigure
go
exec sp_configure 'clr enabled',1;
go
reconfigure
go
if OBJECT_ID('dbo.ufn_chkexp') is not null
drop function dbo.ufn_chkexp;
go
if exists(select * from sys.assemblies where name='CheckExpression')
drop assembly CheckExpression;
go
create assembly CheckExpression authorization dbo
from 'c:\chkexp.dll' with permission_set=safe;
go
create function dbo.ufn_chkexp (@exp nvarchar(4000), @param int)
returns int as external name CheckExpression.CheckExpression.ChkExp;
go
declare @t table (id int identity, name varchar(10), condiction varchar(100));
insert into @t (name,condiction)
select 'A1','@P < 1' union all
select 'A2','@P < 4 AND @P >1' union all
select 'A3','@P >=3 OR @P < 2' union all
select 'A4','@P = 5';
select * from @t where master.dbo.ufn_chkexp(condiction,2)=1;
if object_id('test') is not null drop table test
go
create table test(Id int, Name varchar(10), Condition varchar(20))
insert into test select 1, 'A1', '@P < 1'
insert into test select 2, 'A2', '@P < 4 AND @P >1'
insert into test select 3, 'A3', '@P >=3 OR @P < 2'
insert into test select 4, 'A4', '@P = 5'
go
---------------------------
-- 处理单个逻辑的函数
-- 比如: @P > 1
---------------------------
create function fn_base(@con varchar(20), @par int)
returns bit
as
begin
declare @1 int, @2 int
set @con = replace(@con, '@P', ltrim(@par))
if @con like '%>=%'
begin
select @1 = left(@con, charindex('>',@con)-1), @2 = stuff(@con, 1, charindex('=',@con), '')
if @1 >= @2 return 1
else return 0
end
if @con like '%<=%'
begin
select @1 = left(@con, charindex('<',@con)-1), @2 = stuff(@con, 1, charindex('=',@con), '')
if @1 <= @2 return 1
else return 0
end
if @con like '%>%'
begin
select @1 = left(@con, charindex('>',@con)-1), @2 = stuff(@con, 1, charindex('>',@con), '')
if @1 > @2 return 1
else return 0
end
if @con like '%<%'
begin
select @1 = left(@con, charindex('<',@con)-1), @2 = stuff(@con, 1, charindex('<',@con), '')
if @1 < @2 return 1
else return 0
end
return null
end
go
---------------------------
-- 只考虑两个逻辑条件的情况
-- 复杂逻辑自己扩展了
---------------------------
create function fn_Check(@con varchar(100), @par int)
returns bit
as
begin
if @con like '% AND %'
begin
set @con = replace(@con, ' AND ', '.')
if dbo.fn_base(parsename(@con,2), @par) = 0 return 0
if dbo.fn_base(parsename(@con,1), @par) = 0 return 0
else return 1
end
if @con like '% OR %'
begin
set @con = replace(@con, ' OR ', '.')
if dbo.fn_base(parsename(@con,2), @par) = 1 or dbo.fn_base(parsename(@con,1), @par) = 1 return 1
else return 0
end
return null
end
go
-- Check --
select * from test where dbo.fn_Check(Condition, 2) = 1
/*
Id Name Condition
----------- ---------- --------------------
2 A2 @P < 4 AND @P >1
(1 行受影响)
*/
-- 删除
/*
drop function fn_Check
drop function fn_base
drop table test
*/