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, 3 months ago by Steve Collins.
• This reply was modified 6 years, 3 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, 3 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...

If this can't be done by absolute offset then that raises a bunch of issues.  What can you say about the characters that appear in the 'xxxxxxx xxxxxxx' segments?  Can...

In this string the dash appears in the 33rd (or 32 starting from 0) position:

xxxxxxx xxxxxxxxx : 123456 (xx) - xxxxxxx...

In this string the dash appears in the 36th position:

xxxxxxx...

Why not just look for the dash, "-"?

IIF(InStr(Fields!my.Value,"-")=36, "Paris", "London")