Both approaches are wrong.

Both take resourses DEDICATED to each running process. Both use calculation on fly, both use hardcoded rules.

But if you have single table storing all possible numbers and...

Because it's not a user application. It's a SERVER.

It's a single machine serving potentially thousands of users.

And if every one of them will consume 10% of memory 11th user will...

Do you use system table inserted inside of the trigger?

"Zero" date in VB is 1899-12-30, "Zero" date in SQL Server is 1900-01-01.

Excel has VB on background.

No, you can.

Just declare parameter for sp_executesql as OUTPUT.

SELECT COUNT(case Result when 'Pass' then 1 else NULL end) as Pass,

COUNT(case Result when 'Fail' then 1 else NULL end) as Fail

FROM ...

You gonna need to include also '123D4', '432E1', '$1234', '$4321' and all other possible variations.

> Also the range of the numbers acceptable is encapsulated in my function

You better encapsulate it into static table. All possible numbers, no matter how many millions of the you've...

Looks like connection timeout.

Did you try to run more reasonable query within the same OPENQUERY?

Replace

exec ('use ' + @DestDB + ';UPDATE PS_PV_SRCH_RUN_CTL SET FILE_DIRECTORY = ''\\serverxxx\psoft\verity\''' + @DestDB + '''')

with

print 'use ' + @DestDB + ';UPDATE PS_PV_SRCH_RUN_CTL SET FILE_DIRECTORY = ''\\serverxxx\psoft\verity\''' +...

> [here, I need to include 'and column2=@param1' IF param1 is not null, otherwise ignore @param1]

That's exactly what my script is doing.

SET @DesiredAge = NULLIF(@DesiredAge , '')

SELECT ....

WHERE (somedata = @DesiredAge OR @DesiredAge IS NULL)

Show where?

Function may have only one outcome - its result.

I tell you again - debug not function, debug the code in function.

Populate it with what?

Is it a question for SQL 2005 forum?