Yet another handy use of a tally table.

https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

drop table if exists dbo.test_TID;
go
create table dbo.test_TID(
  TIDPrefix     varchar(20) not null);

insert dbo.test_TID(TIDPrefix) values
('A[123xyz]'),
('x[UY345]');

select 
 ...

• This reply was modified 5 years, 9 months ago by Steve Collins. Reason: Exclude rows without brackets

I've decided to add my name to the list of Steve's around here.  My first post on SSC was about 1 year ago.  When I signed up I...

I've decided to add my name to the list of Steve's around here.  My first post on SSC was about 1 year ago.  When I signed up I wasn't sure...

Oops wrong thread

No... you recommended using 2 queries.  Not two procedures.  If you go further up, you'll see that...

Always good to hear differing methods. Optional parameters are the bane of my life, but that's a reasonable well established method of dealing with it, and fairly low cost...

Why questions are tricky!  

Ha, my recommendation is 2 procs so there's nothing to confuse.  If the OP wants to keep dealing with avoidable...

Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).

So presumably @Parameter is optional and can be NULL. In which case the...

It's not the same query as the one the OP posted.  NULL does not equal NULL

Not quite the same as what the OP posted.  Here's another way but readability is not...

Imo it's easier to express as 2 queries

drop table if exists test_tblinfo;
go
create table test_tblinfo(fldinfo   int); 
go

insert test_tblinfo values (null), (1);

declare
  @parameter   ...

https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

https://www.sqlservercentral.com/blogs/tally-tables-in-t-sql

declare
  @start_yr         int=2020,
  @years            int=20;


;with
tally(n) as
(
...

The OP could generate as many years as needed with a tally table 🙂

declare
  @start_yr         int=2020,
  @years...

• This reply was modified 5 years, 9 months ago by Steve Collins. Reason: union all

Fwiw, adding meta data like ('ORD'+number and 'MO'+date) to database keys is not the best practice.  If you're stuck with it perhaps it's possible to add constraints which only allow...

• This reply was modified 5 years, 9 months ago by Steve Collins.