Dynamic Expresssion evaluation in functions

• vinod.andani-874416

  Mr or Mrs. 500

  Points: 537

  More actions

  October 1, 2015 at 8:33 am

  #99013

  What I'm trying to build is a function which dynamically evaluates the expression on string based on the operators passed dynamically. I can easily do this using dynamic sql in stored proc / anonymous block (see below). But, I what this in function as I want to call the function in SELECT statement for the table for each records and determine which record passes the condition and which not..

  declare @value varchar(255) = 'asasas',

  @len varchar(5) = '',

  @operators varchar(2)= '=',

  @cond varchar(25) = '',

  @sql nvarchar(1000) = '';

  -- if length is not specified then assume the len = len(string)

  select @len = case when @len is null or @len = 0 or @len = '' then len(@value) else @len end;

  select @value = ltrim(rtrim(@value));

  -- Construct the dynamic sql statement

  select @sql = N'select case when ''' + @value + ''' not like ''%[^A-Za-z]%'' and len(''' + @value + ''') ' +@operators + ' ' + @len + ' then 1 else 0 end test ';

  print @sql

  exec sp_executesql @sql

  The above code works for any operator you supply '=', '>', '<' etc... But such dynamic evaluation I want in function rather than in stored proc.

  Any pointers / corrections much appreciated.

  Thanks

  -Vinod