Hi GSquared,

This is meant to be run one time to create the procs, that are then used to get the data. Of course, they need some modifications to add a...

How about some dynamic sql to dynamically create the stored procedure for doing this properly instead?

SELECT 'CREATE PROCEDURE dbo.[spGet_' + ss.NAME + '_' + st.NAME + '] AS SELECT '...

I took your code, formatted it with SQL Prompt Pro, and then eliminated three of your cursors. Look it over, see if it works correctly, and then see if you...

peterzeke (5/31/2011)

WayneS (5/27/2011)

peterzeke (5/27/2011)

Starting with #2...

Good - you've gotten the business logic down. Now, if you'll post the table definitions, sample data, and the c.u.r.s.o.r. code, we can help you do it fast.

Good question, with excellent false answers.

This appears to be easily solved with:

SELECT [Item Code], Qty = COUNT(*)

FROM [a Certain Table]

GROUP...

Grant Fritchey (5/29/2011)

WayneS (5/28/2011)

Grant Fritchey (5/28/2011)

WayneS (5/28/2011)

Jim Murphy (5/27/2011)

BTW, this (an administrative task) is actually one of the few good uses of a cursor. If you find yourself going to a cursor for other data retrieval operations, STOP....

hxkresl (5/28/2011)

Jeff Moden (5/28/2011)

Roy Ernest (5/27/2011)

GSquared (5/27/2011)

SQLkiwi (5/26/2011)

Steve Jones - SSC Editor (5/26/2011)

Here's the profiler run on my more modern computer between my code and Jeff's code:

The things that I'm curious about:

1. Why aren't the # of reads between runs on your...

Perhaps an easier to understand way of saying this...

Okay, I converted my earlier code to run with the sample table that Jeff created (removed one cte to create the RN column; renamed RN to SomeID throughout):

;

WITH cte AS

(

--...

Jeff Moden (5/27/2011)

WayneS (5/27/2011)

Sean Lange (5/27/2011)