Solved

How to get results with CTE query rahter than Loop

Posted on 2014-02-23
1
352 Views
Last Modified: 2014-02-28
I have created a stored procedure with loops that gives me the output I need, but it is slow. I have tried to use a CTE query, but do not know how to create the TotalSamples. The CTE creates the same number of rows as the looped query, but does not calculate TotalSamples. TotalSamples should be the distinct samplekey by site, collectionYear, germinationYear, plot, habitat
speciesBySiteTeatmentWithLoop.sql
speciesBySiteTreatment.sql
sampleSeedbank50.csv
createSeedbank.sql
siteTreatmentSpeciesSampleOutput.csv
siteTreatmentSpeciesOutputCTE.csv
0
Comment
Question by:dblankman
1 Comment
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 39880842
Hi Blankman,

A CTE won't necessarily replace a store procedure or the necessity of looping on data.  What the CTE does is allow you to break a complex query into smaller pieces and reuse some of the intermediate results without the database rebuilding those results.

If you can't visualize a query that will do what you want, neither a complex query nor a CTE will do what you want any better than your stored procedure.


Kent
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question