Single Parameter SP assistance needed 1

[Idokyoku2]

Hello,
I've written an SP which conducts an analysis for each Bulk Mail Center (BMC). This lets us know which project provides postage savings based on total by drop shipment destination(BMC). It currently works well.
However, I need assistance figuring out how to include a single input parameter. This needs to run on any table within a DB, without altering each tbl name, for each statement, every time it's executed.

An example may clarify:

USE HVACR
GO
ALTER PROCEDURE BMCANALYSIS
AS
SET NOCOUNT ON

-- BMC NEW JERSEY, NJ
DECLARE MYCRSR1 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT))AS BMC_NEW_JERSEY_NJ_00102
FROM TBL1
WHERE ZIP LIKE '005%'
OR ZIP BETWEEN '06800' AND '07999'
OR ZIP BETWEEN '08500' AND '11999'
OR ZIP BETWEEN '12400' AND '12799'
OR ZIP LIKE '340%'
GROUP BY ZIP, ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR1
FETCH NEXT FROM MYCRSR1
CLOSE MYCRSR1
DEALLOCATE MYCRSR1

-- BMC JACKSONVILLE, FL
DECLARE MYCRSR2 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_JACKSONVILLE_FL_32099
FROM TBL1
WHERE ZIP BETWEEN '00600' AND '00999'
OR ZIP LIKE '299%'
OR ZIP BETWEEN '31300' AND '31699'
OR ZIP BETWEEN '32000' AND '33999'
OR ZIP LIKE '341%'
OR ZIP LIKE '342%'
OR ZIP LIKE '344%'
OR ZIP LIKE '346%'
OR ZIP LIKE '347%'
OR ZIP LIKE '349%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR2
FETCH NEXT FROM MYCRSR2
CLOSE MYCRSR2
DEALLOCATE MYCRSR2

-- BMC SPRINGFIELD, MA
DECLARE MYCRSR3 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_SPRINGFIELD_MA_05500
FROM TBL1
WHERE ZIP BETWEEN '01000' AND '06799'
OR ZIP BETWEEN '12000' AND '12399'
OR ZIP LIKE '128%'
OR ZIP LIKE '129%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR3
FETCH NEXT FROM MYCRSR3
CLOSE MYCRSR3
DEALLOCATE MYCRSR3

-- BMC PHILADELPHIA, PA
DECLARE MYCRSR4 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_PHILADELPHIA_PA_19205
FROM TBL1
WHERE ZIP BETWEEN '08000' AND '08499'
OR ZIP BETWEEN '13700' AND '13999'
OR ZIP BETWEEN '16900' AND '19999'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR4
FETCH NEXT FROM MYCRSR4
CLOSE MYCRSR4
DEALLOCATE MYCRSR4

-- BMC PITTSBURGH, PA
DECLARE MYCRSR5 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_PITTSBURGH_PA_15195
FROM TBL1
WHERE ZIP BETWEEN '13000' AND '13699'
OR ZIP BETWEEN '14000' AND '16899'
OR ZIP BETWEEN '26000' AND '26699'
OR ZIP BETWEEN '43900' AND '44799'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR5
FETCH NEXT FROM MYCRSR5
CLOSE MYCRSR5
DEALLOCATE MYCRSR5

-- BMC WASHINGTON, DC
DECLARE MYCRSR6 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_WASHINGTON_DC_20499
FROM TBL1
WHERE ZIP BETWEEN '20000' AND '21299'
OR ZIP BETWEEN '21400' AND '23999'
OR ZIP LIKE '244%'
OR ZIP LIKE '254%'
OR ZIP LIKE '267%'
OR ZIP LIKE '268%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR6
FETCH NEXT FROM MYCRSR6
CLOSE MYCRSR6
DEALLOCATE MYCRSR6

-- BMC GREENSBORO, NC
DECLARE MYCRSR7 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_GREENSBORO_NC_27075
FROM TBL1
WHERE ZIP BETWEEN '24000' AND '24399'
OR ZIP BETWEEN '24500' AND '24999'
OR ZIP BETWEEN '27000' AND '29799'
OR ZIP LIKE '376%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR7
FETCH NEXT FROM MYCRSR7
CLOSE MYCRSR7
DEALLOCATE MYCRSR7

-- BMC CINCINATTI, OH
DECLARE MYCRSR8 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_CINCINNATI_OH_45900
FROM TBL1
WHERE ZIP BETWEEN '25000' AND '25399'
OR ZIP BETWEEN '25500' AND '25999'
OR ZIP BETWEEN '40000' AND '41899'
OR ZIP LIKE '421%'
OR ZIP LIKE '422%'
OR ZIP BETWEEN '42500' AND '42799'
OR ZIP BETWEEN '43000' AND '43399'
OR ZIP LIKE '437%'
OR ZIP LIKE '438%'
OR ZIP BETWEEN '44800' AND '46299'
OR ZIP BETWEEN '46900' AND '47499'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR8
FETCH NEXT FROM MYCRSR8
CLOSE MYCRSR8
DEALLOCATE MYCRSR8

-- BMC ATLANTA, GA
DECLARE MYCRSR9 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_ATLANTA_GA_31195
FROM TBL1
WHERE ZIP LIKE '298%'
OR ZIP BETWEEN '30000' AND '31299'
OR ZIP BETWEEN '31700' AND '31999'
OR ZIP BETWEEN '35000' AND '35299'
OR ZIP BETWEEN '35400' AND '36899'
OR ZIP LIKE '373%'
OR ZIP LIKE '374%'
OR ZIP BETWEEN '37700' AND '37999'
OR ZIP LIKE '398%'
OR ZIP LIKE '399%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR9
FETCH NEXT FROM MYCRSR9
CLOSE MYCRSR9
DEALLOCATE MYCRSR9

-- BMC MEMPHIS, TN
DECLARE MYCRSR10 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_MEMPHIS_TN_38999
FROM TBL1
WHERE ZIP BETWEEN '36900' AND '37299'
OR ZIP LIKE '375%'
OR ZIP BETWEEN '38000' AND '39799'
OR ZIP LIKE '700%'
OR ZIP LIKE '701%'
OR ZIP BETWEEN '70300' AND '70599'
OR ZIP LIKE '707%'
OR ZIP LIKE '708%'
OR ZIP LIKE '713%'
OR ZIP LIKE '714%'
OR ZIP LIKE '716%'
OR ZIP LIKE '717%'
OR ZIP BETWEEN '71900' AND '72999'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR10
FETCH NEXT FROM MYCRSR10
CLOSE MYCRSR10
DEALLOCATE MYCRSR10

-- BMC ST LOUIS, MO
DECLARE MYCRSR11 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_ST_LOUIS_MO_63299
FROM TBL1
WHERE ZIP LIKE '420%'
OR ZIP LIKE '423%'
OR ZIP LIKE '424%'
OR ZIP BETWEEN '47500' AND '47999'
OR ZIP BETWEEN '61400' AND '62099'
OR ZIP BETWEEN '62200' AND '63199'
OR ZIP BETWEEN '63300' AND '63999'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR11
FETCH NEXT FROM MYCRSR11
CLOSE MYCRSR11
DEALLOCATE MYCRSR11

-- BMC DETROIT, MI
DECLARE MYCRSR12 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_DETROIT_MI_48399
FROM TBL1
WHERE ZIP BETWEEN '43400' AND '43699'
OR ZIP BETWEEN '46500' AND '46899'
OR ZIP BETWEEN '48000' AND '49799'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR12
FETCH NEXT FROM MYCRSR12
CLOSE MYCRSR12
DEALLOCATE MYCRSR12

-- BMC CHICAGO, IL
DECLARE MYCRSR13 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_CHICAGO_IL_60808
FROM TBL1
WHERE ZIP LIKE '463%'
OR ZIP LIKE '464%'
OR ZIP BETWEEN '53000' AND '53299'
OR ZIP LIKE '534%'
OR ZIP LIKE '535%'
OR ZIP BETWEEN '53700' AND '53999'
OR ZIP BETWEEN '60000' AND '61199'
OR ZIP LIKE '613%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR13
FETCH NEXT FROM MYCRSR13
CLOSE MYCRSR13
DEALLOCATE MYCRSR13

-- BMC MINNEAPOLIS, MN
DECLARE MYCRSR14 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_MINNEAPOLIS_MN_55202
FROM TBL1
WHERE ZIP LIKE '498%'
OR ZIP LIKE '499%'
OR ZIP BETWEEN '54000' AND '55199'
OR ZIP BETWEEN '55300' AND '56799'
OR ZIP BETWEEN '58000' AND '58899'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR14
FETCH NEXT FROM MYCRSR14
CLOSE MYCRSR14
DEALLOCATE MYCRSR14

-- BMC DES MOINES, IA
DECLARE MYCRSR15 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_DES_MOINES_IA_50999
FROM TBL1
WHERE ZIP BETWEEN '50000' AND '51699'
OR ZIP BETWEEN '52000' AND '52899'
OR ZIP BETWEEN '57000' AND '57799'
OR ZIP LIKE '612%'
OR ZIP LIKE '680%'
OR ZIP LIKE '681%'
OR ZIP BETWEEN '68300' AND '68999'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR15
FETCH NEXT FROM MYCRSR15
CLOSE MYCRSR15
DEALLOCATE MYCRSR15

-- BMC DENVER, CO
DECLARE MYCRSR16 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_DENVER_CO_80088
FROM TBL1
WHERE ZIP BETWEEN '59000' AND '59999'
OR ZIP BETWEEN '69000' AND '69399'
OR ZIP BETWEEN '80000' AND '81699'
OR ZIP BETWEEN '82000' AND '83499'
OR ZIP LIKE '836%'
OR ZIP LIKE '837%'
OR ZIP BETWEEN '84000' AND '84799'
OR ZIP LIKE '865%'
OR ZIP BETWEEN '87000' AND '87599'
OR ZIP BETWEEN '87700' AND '88499'
OR ZIP LIKE '898%'
OR ZIP LIKE '979%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR16
FETCH NEXT FROM MYCRSR16
CLOSE MYCRSR16
DEALLOCATE MYCRSR16

-- BMC, KANSAS CITY, KS
DECLARE MYCRSR17 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_KANSAS_CITY_KS_64399
FROM TBL1
WHERE ZIP LIKE '640%'
OR ZIP LIKE '641%'
OR ZIP BETWEEN '64400' AND '65899'
OR ZIP BETWEEN '66000' AND '66299'
OR ZIP BETWEEN '66400' AND '67999'
OR ZIP LIKE '739%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR17
FETCH NEXT FROM MYCRSR17
CLOSE MYCRSR17
DEALLOCATE MYCRSR17

-- BMC DALLAS, TX
DECLARE MYCRSR18 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_DALLAS_TX_75199
FROM TBL1
WHERE ZIP LIKE '706%'
OR ZIP BETWEEN '71000' AND '71299'
OR ZIP LIKE '718%'
OR ZIP LIKE '730%'
OR ZIP LIKE '731%'
OR ZIP BETWEEN '73300' AND '73899'
OR ZIP LIKE '740%'
OR ZIP LIKE '741%'
OR ZIP BETWEEN '74300' AND '79999'
OR ZIP LIKE '885%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR18
FETCH NEXT FROM MYCRSR18
CLOSE MYCRSR18
DEALLOCATE MYCRSR18

-- BMC SEATTLE, WA
DECLARE MYCRSR19 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_SEATTLE_WA_98000
FROM TBL1
WHERE ZIP LIKE '835%'
OR ZIP LIKE '838%'
OR ZIP BETWEEN '97000' AND '97899'
OR ZIP BETWEEN '98000' AND '98699'
OR ZIP BETWEEN '98800' AND '99999'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR19
FETCH NEXT FROM MYCRSR19
CLOSE MYCRSR19
DEALLOCATE MYCRSR19

-- BMC LOS ANGELES, CA
DECLARE MYCRSR20 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_LOS_ANGELES_CA_90901
FROM TBL1
/* THESE ZIPS QUALIFY FOR LABMC DESTINATION ENTRY DISCOUNTS, BUT ONLY
WHEN ENTRY POINT'S ARE WITHIN AREAS 800-820, 822-831, 835, 838-884, 889-978 */
WHERE ZIP LIKE '850%'
OR ZIP LIKE '852%'
OR ZIP LIKE '853%'
OR ZIP BETWEEN '85500' AND '85799'
OR ZIP LIKE '859%'
OR ZIP LIKE '860%'
OR ZIP LIKE '863%'
OR ZIP LIKE '864%'
-- THESE ZIPS QUALIFY FOR LABMC UNDER REGULAR CIRCUMSTANCES
OR ZIP BETWEEN '88900' AND '89199'
OR ZIP LIKE '893%'
OR ZIP BETWEEN '90000' AND '90899'
OR ZIP BETWEEN '91000' AND '92899'
OR ZIP BETWEEN '93000' AND '93599'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR20
FETCH NEXT FROM MYCRSR20
CLOSE MYCRSR20
DEALLOCATE MYCRSR20

-- BMC SAN FRANCISCO, CA
DECLARE MYCRSR21 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_SAN_FRANCISCO_CA_94850
FROM TBL1
WHERE ZIP LIKE '894%'
OR ZIP LIKE '895%'
OR ZIP LIKE '897%'
OR ZIP BETWEEN '93600' AND '96999'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR21
FETCH NEXT FROM MYCRSR21
CLOSE MYCRSR21
DEALLOCATE MYCRSR21



My result set is like so when executed:

BMC_NEW_JERSEY_NJ_00102
-----------------------
1052

BMC_JACKSONVILLE_FL_32099
-------------------------
4316

BMC_SPRINGFIELD_MA_05500
------------------------
3225

BMC_PHILADELPHIA_PA_19205
-------------------------
955

BMC_PITTSBURGH_PA_15195
-----------------------
935

BMC_WASHINGTON_DC_20499
-----------------------
2568

BMC_GREENSBORO_NC_27075
-----------------------
3369

BMC_CINCINNATI_OH_45900
-----------------------
2059

BMC_ATLANTA_GA_31195
--------------------
6790

BMC_MEMPHIS_TN_38999
--------------------
2250

BMC_ST_LOUIS_MO_63299
---------------------
1208

BMC_DETROIT_MI_48399
--------------------
1121

BMC_CHICAGO_IL_60808
--------------------
1740

BMC_MINNEAPOLIS_MN_55202
------------------------
2049

BMC_DES_MOINES_IA_50999
-----------------------
1368

BMC_DENVER_CO_80088
-------------------
1276

BMC_KANSAS_CITY_KS_64399
------------------------
1348

BMC_DALLAS_TX_75199
-------------------
2696

BMC_SEATTLE_WA_98000
--------------------
615

BMC_LOS_ANGELES_CA_90901
------------------------
7018

BMC_SAN_FRANCISCO_CA_94850
--------------------------
2896

I'd like to be able to do something like this:
EXEC BMCANALYSIS '@ANYINPUTPARAMETERNAME'

Which will then replace all statement table names to be like the input parameter. Not to sure this is possible?

-- BMC NEW JERSEY, NJ
DECLARE MYCRSR1 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT))AS BMC_NEW_JERSEY_NJ_00102
FROM '@ANYINPUTPARAMETERNAME'
WHERE ZIP LIKE '005%'
OR ZIP BETWEEN '06800' AND '07999'
OR ZIP BETWEEN '08500' AND '11999'
OR ZIP BETWEEN '12400' AND '12799'
OR ZIP LIKE '340%'
GROUP BY ZIP, ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR1
FETCH NEXT FROM MYCRSR1
CLOSE MYCRSR1
DEALLOCATE MYCRSR1

-- BMC JACKSONVILLE, FL
DECLARE MYCRSR2 CURSOR
FOR
SELECT COUNT(CAST(ZIP AS SMALLINT)) AS BMC_JACKSONVILLE_FL_32099
FROM 'SAME_INPUT_PARAMETER_NAME_AS_BMC_NEW_JERSEY_NJ'
WHERE ZIP BETWEEN '00600' AND '00999'
OR ZIP LIKE '299%'
OR ZIP BETWEEN '31300' AND '31699'
OR ZIP BETWEEN '32000' AND '33999'
OR ZIP LIKE '341%'
OR ZIP LIKE '342%'
OR ZIP LIKE '344%'
OR ZIP LIKE '346%'
OR ZIP LIKE '347%'
OR ZIP LIKE '349%'
GROUP BY ZIP WITH ROLLUP
ORDER BY COUNT(CAST(ZIP AS INT))DESC
OPEN MYCRSR2
FETCH NEXT FROM MYCRSR2
CLOSE MYCRSR2
DEALLOCATE MYCRSR2

And so on for the balance of the procedure.

Any suggestions would be greatly appreciated.

Thanks,
David

 

[donutman]

I have to say, you have one of the most amazing uses for a cursor that I've ever seen. Two questions:
[ul][li]How many different tables will this parameter refer to?

[/li][li]Except for the table variable, would you like to do all of this in 1 query without any cursors?[/li][/ul]
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Idokyoku2]

Thanks! I think?

It will refer to only one table for each execution.

I merely used cursors as seen to capture the total records for zips selected. If you have a better suggestion, fire away. I'm always open to new ideas.

David

 

[donutman]

Still don't know how many tables you want to run through this SP. But as far as eliminating the cursor, the first step is to build a custom (I think it's custom) zip code directory. For every possible zip code you need to have the UNIQUE city name that you intend to use to get the zip code counts that you want. Can you do that? The next question is what exactly do you want counted? It looks like the total number of rows for each city is that right?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Idokyoku2]

The input parameter is to replace tbl1 for every instance of tbl1 in my proc. See original post.

Not to sure what you mean by custom zip code directory, and not to sure I can do that. Could you provide a sampling of how to get around the cursor, etc. Maybe I can? I was trying different things and ended on my current proc simply because it worked for me.

I am looking for total number of records that apply to the zip range specified for each city(BMC).

Your post is greatly appreciated.

BTW, all of this work is so I can present this to the CEO and Brass to replace 1986 software currently being used by our D.P. Manager. He's slightly resistant to change and learning new things. But, I must first provide samples without bugs to show how many steps we can eliminate by using SP's.

David

 

[donutman]

Your first post suggested that you are going to run your procedure with a variable table name. I'm asking how many table names do you intend to set that variable to. I'm NOT asking how many different tables there are WITHIN the procedure. I can see that there is only ONE.
You can easily build the zip code directory, the question is are you willing to? The only problem is that you may have to update it if you don't already have a file with all of the possible zip codes that you might encounter in the future. This is very important, so don't ignore this issue or else you will get incorrect totals in the future.
Here is how you would build it for the existing table for the City of New Jersey.

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] ZipcodeDirectory
   [Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] Zip[Gray],[/Gray] [red]'New Jersey'[/red] [Blue]FROM[/Blue] TBL1
      [Blue]WHERE[/Blue] ZIP [Gray]LIKE[/Gray] [red]'005%'[/red]
         [Gray]OR[/Gray] ZIP [Blue]BETWEEN[/Blue] [red]'06800'[/red] [Gray]AND[/Gray] [red]'07999'[/red]
         [Gray]OR[/Gray] ZIP [Blue]BETWEEN[/Blue] [red]'08500'[/red] [Gray]AND[/Gray] [red]'11999'[/red]
         [Gray]OR[/Gray] ZIP [Blue]BETWEEN[/Blue] [red]'12400'[/red] [Gray]AND[/Gray] [red]'12799'[/red]
         [Gray]OR[/Gray] ZIP [Gray]LIKE[/Gray] [red]'340%'[/red]

You can do a similar insert for every city of interest and leaving the remaining ones blank if you wish. As you can see the ZipcodeDirectory table has two columns. You should make the Zipcode column the same datatype as in the TBL1 table and set it as the primary key of ZipcodeDirectory.
You avoid the cursor by performing an inner join between TBL1 and ZipcodeDirectory on the ZIP column and group by City. A simple count(ZIP) will give you totals for each city. Do you need further help?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Idokyoku2]

Looks like good stuff. Not quite sure how this will work with a completely different table names being imported daily? Let me give this a whirl.

Thanks,
David

 

[donutman]

I thought of an easy way to avoid the problem of new zip codes in the future. Just combine all of the Insert statements that you use to create the ZipcodeDirectory into one SP. Then modify the Select statements to include a left join on the ZipcodeDirectory table and insert only those rows that have a Null in the ZipcodeDirectory. That has the effect of adding only those rows that don't already exist in the ZipcodeDirectory. You then run that SP prior to your counting SP.
You want to change the table name every day. OK, then the next step is to design a DTS job that is run per schedule and modifies the table name each day. I'm not an expert with DTS, so someone else could help you with that. It would be best to post in that forum.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Idokyoku2]

Wow! All great stuff. I got side tracked with another project and haven't done anything just yet.

I like trying new things as they may not always work for current projects, but usually lend themselves to others.

Your efforts are truly appreciated.

David

 

[Idokyoku2]

Donutman,
instead of trying to alter all tbl names in my proc to the input parameter, why can't I simply add two EXEC sp_rename's (one at the beginning and end)? That would change the table name I wish to analyze to tbl1 during the duration of the sp. It will then rename it to it's original name.

I've already added one for the top and bottom, all compiles fine. But can't figure out how to declare a table variable without errors?
David

 

[donutman]

You have to use dynamic SQL to have a variable table name. It's best to avoid that if possible, but for your situation I don't see how it can be avoided. That being said, I would consider going the DTS route which is basically the same thing.
Get the SP working with a fixed table name, then move on to the next step. Don't try to accomplish all in one fell-swoop.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Idokyoku2]

I suppose I could just rename to 'tbl1', exec sp and then back to the original. Fairly painless.

Have a good weekend!

Thanks,
David