Set operators work best when the tables are identical.  In this case there are different collations between tables.  Since "we're talking about thousands of columns across 100s of tables" it...

The procedure could be named whatever you wish.  "spFindExistingOrCreateNewProject" seems ok but a maybe little long...

If the goal is to tighten/shorten the code then here are a few suggestions:

1. Where...

Currently there are 2 procs:

Proc 1 ("spNewProject") does 3 things: 1) returns all project records if all inputs are nulls, 2) if necessary creates new project and returns new record,...

Are you certain so much individual error checking is necessary?  Sometimes it can be less wordy to "jfdi", or just f---ing do it.  Whatever you're trying to do once all...

Hi there,

"The OLE DB provider "MSDASQL" for linked server..."  Are you using the “OLE DB Provider for ODBC Drivers”?  MSDASQL is a very old interface, it's either deprecated or on...

There are a bunch of issues with the code you've posted.  Have a look at this code (I don't have 2008 so please un-d.i.e the objects).  The 2 procs are...

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

Does the REG.MENU table have a primary key?  If so wouldn't you want to output that from the procedure as well?

There are only 4 input scenarios?  If so it's just as easy to not use dynamic sql.  This proc allows null inputs and filters in 4 ways (including no filter):

Have a look at this code:

declare
  @start_dtdate,
  @frequencyint,
  @end_dtdate;

select
  @start_dt='2019-09-01',
  @frequency=14;

/* the end_dt is always set to the last day of the...

"WHERE crc.[value] < parms.end_dt" was not necessary at all so I deleted it.  Your function obviated that necessity.  I wrote that but then I pasted it in with...

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

What have you tried?  As I recall SSMS export will not properly escape the text.  I'm pretty sure tho SSIS csv export does.

Your proc produced the correct output.  I'm still trying to get my head around cross apply.  It's kind of magical.  The last part, where not exists, I was really sure...

Danke schön!  Nicely done -- it's in my toolbox now.

We have a project coming up where I'll have the chance to alter or replace the following proc (which runs in...

IIF(Mid(Fields!my.Value, 30, 1)="(","London", "Paris")

or (because the position counts counted the brackets):

IIF(Mid(Fields!my.Value, 28, 1)="(","London", "Paris")

Just to be clear there are 3 possible patterns?

1. Working Reference : [123456] (text in brackets) -  loads more text
2. Working Reference : [123456789] (text in brackets) -  loads more text
3. Working...