One reason the first proc is not functional is because the temporary table is out of scope.  Where does #ssrsresults get created?  If a temp table only has 1 '#'...

Yep

declare...

This is done.  I couldn't find any satisfactory solution to placeholder replacement so the string formatting still happens in C#.  All of the text files and templates have been moved...

I think I see what you are saying about how many times the function could execute and the fact that I don't have a FROM clause.  Are you saying...

If you run it a million times.  In this case here the OP has no 'from' clause so it only executes once.  I actually did read your article a while...

If you're looking for something simple a scalar function could work.  The calculation requires no rows or columns.  If you know the function is accessed by query(s) in a proc...

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