[dba-SQLServer] Stored Procedure help

[John Colby]

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.

On Thu, Oct 8, 2015 at 7:39 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> Ahah - missed the fact that it is the use of CTEs that is casuing the
> problem.
>
> It would have helped if you had told us the error message when trying to
> create the SP.
>
> I presume it is "Common table expression defined but not used."
>
> Yep, that's a requirement of CTEs. If you define them, you must use them
> in some
> sort of query after the definition.   Sure you can fool SQL Server into
> thinking
> you have used it with something like "SELECT 1", but that is not optimal.
>
> --
> Stuart
>
>
> On 9 Oct 2015 at 7:48, Stuart McLachlan wrote:
>
> >
> > That doesn't make sense.  You don't have to have a select in a stored
> > procedure at all.
> >
> > This works fine for me:
> >
> > CREATE PROCEDURE test2
> > AS
> > BEGIN
> > RETURN 1
> > END
> > GO
> >
> >
> > But if you really want a select, this works just as well:
> >
> > CREATE PROCEDURE test3
> > AS
> > BEGIN
> > SELECT 1
> > END
> > GO
> >
> >
> >
> > On 8 Oct 2015 at 12:21, John Colby wrote:
> >
> > > Yes but this doesn't work at the bottom of my stored procedure, I
> > > have already tried it.
> > >
> > > I have perhaps 10 Common Table Expressions.  At the bottom, below
> > > all of those CTEs I MUST HAVE a select statement or the compiler
> > > complains.
> > >
> > > The select statement can be anything, it doesn't have to reference
> > > one of the CTEs but it has to be a select.
> > >
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
John W. Colby
Colby Consulting