[dba-SQLServer] Stored Procedure help

[Salakhetdinov Shamil]

>>>  Perhaps have a base SP which builds up  the base data,
John --

I'd use table-valued UDFs here, e.g. http://tinyurl.com/nlfxcfj
See also  http://tinyurl.com/q5pctua  to get informed what UDFs use cases to avoid and  http://tinyurl.com/nkhwrj  - to find out what are right for you to go ...

Enjoy! :)

-- Shamil


>Thursday, October  8, 2015 9:34 PM -04:00 from John Colby <jwcolby at gmail.com>:
>
>And if you do "fool it" then all the CTEs defined are no longer valid after
>that.
>
>I am building up a report from a set of two tables, addresses and old
>addresses.  The objective is to find moves, then if any exist the "old
>address".  Having found the old address(es) then I have to select the most
>recent "old address" (there may be several).  From that I have to compute a
>distance between the latest address and the most recent old address.
>Having THAT, I have to count the move records grouped by MSA, State, State
>old / State new, distance code etc.  So there is a ton of stuff "building
>up a base data set, then a set of several queries which simply take that
>data and count it grouping by different things.
>
>To make matters more difficult I have to pass in parameters for which
>months (move year/month) I want to perform these reports over.  CTEs make
>this all somewhat manageable since I can pass in the parameters and build
>the CTEs to select the correct set of move records, then build up on those
>to join to the old move table, perform an Over to select the latest of the
>old move records for each new move record, then build a CTE for each final
>report.
>
>I have to rethink this whole thing.  Perhaps have a base SP which builds up
>the base data, then another SP which takes the ReportName parameter, calls
>the first SP to get the basic data, and using that grab the correct final
>report data.  Or something. <<< skipped >>>
>