October 3, 2010 at 8:28 am
Hi all,
I've got sections of code I'd like to transfrom into a single stored procedure. At the moment, we run each of the five sections separately, the first is a join (into which we paste a bunch of phone numbers )that yields phone no's and a text string, the next two are inserts of that join's result-set into two tables (OutQueue and Block), the fourth is a result check that updates comments column of the Block table and does a rowcount, and the last is an update that updates a 'Comment' column, then runs a check that is possibly not needed.
What I'd like to know is:
1) what variables will be needed (ie for the Rowcount, and how I can send that off for reporting);
2) how to avoid repeating the blocks of phone numbers;
3) whether the last check is necessary;
4) the best overall way to write the Sproc.
Here's the code so far, I'd be grateful for all your help, people!
Cheers,
Jim.
---------------------------
-- Initial paste of phone no's + join of message text--,
---------------------------
SELECT *
FROM (
SELECT '27724748733' AS PHONE UNION
SELECT '27824474244' AS PHONE UNION
) a
INNER JOIN Outqueue2 b
ON b.phone = a.phone AND
b.msg = 'Your membership has been cancelled, DO NOT REPLY.'
ORDER BY a.phone
---------------------------
-- Insert Confirmation MT
---------------------------
GO
INSERT INTO DBSRVR2.Queues.dbo.Outqueue
( phone, routing, service, keyword, keyid, msg, result, origin )
SELECT a.phone, c.freeOffAggMt AS routing,
ISNULL(d.service, 0) AS service,
ISNULL(d.keyword, 0) AS keyword,
ISNULL(d.keyid, 0) AS keyid,
'Your membership has been cancelled, DO NOT REPLY.' AS msg,
61 AS result, 3 AS origin
FROM (
SELECT '27724748733' AS PHONE UNION
SELECT '27824474244' AS PHONE UNION
) a
INNER JOIN DBSRVR1.OUTNOW.DBO.Phonelist b (read committed snapshot)
ON b.phone = a.phone
INNER JOIN DBSRVR1.OUTNOW.DBO.DefineMtRoutes c (read committed snapshot)
ON c.aggregator = b.aggregator AND
c.network = b.network
LEFT JOIN DBSRVR1.OUTNOW.DBO.Services d (read committed snapshot)
ON d.userid = b.userid AND
d.service = b.srvspref
---------------------------
-- Insert into BLOCK
---------------------------
GO
INSERT INTO DBSRVR2.QUEUES.DBO.Block
( phone, aggregator, comment )
SELECT x.phone, 0 AS aggregator, 'WASP complaint (BKM)' AS comment
-- Rowcount (for future reporting)
SELECT COUNT(*) AS total
FROM (
SELECT '27724748733' AS PHONE UNION
SELECT '27824474244' AS PHONE UNION
) x
LEFT JOIN Block y (NOLOCK)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (y.phone IS NULL)
---------------------------
-- Check results
---------------------------
GO
UPDATE y
SET comment = 'WASP complaint (BKM)'
--
SELECT y.*
FROM (
SELECT '27724748733' AS PHONE UNION
SELECT '27824474244' AS PHONE UNION
) x
INNER JOIN Block y (read committed snapshot)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (comment LIKE '%comment provided')
October 3, 2010 at 1:00 pm
blacklabellover2003 (10/3/2010)
Hi all,I've got sections of code I'd like to transfrom into a single stored procedure. At the moment, we run each of the five sections separately, the first is a join (into which we paste a bunch of phone numbers )that yields phone no's and a text string, the next two are inserts of that join's result-set into two tables (OutQueue and Block), the fourth is a result check that updates comments column of the Block table and does a rowcount, and the last is an update that updates a 'Comment' column, then runs a check that is possibly not needed.
What I'd like to know is:
1) what variables will be needed (ie for the Rowcount, and how I can send that off for reporting);
2) how to avoid repeating the blocks of phone numbers;
3) whether the last check is necessary;
4) the best overall way to write the Sproc.
Here's the code so far, I'd be grateful for all your help, people!
Cheers,
Jim.
---------------------------
-- Initial paste of phone no's + join of message text--,
---------------------------
SELECT *
INTO #Temp
FROM (
SELECT '27724748733' AS PHONE UNION
SELECT '27824474244' AS PHONE UNION
) a
INNER JOIN Outqueue2 b
ON b.phone = a.phone AND
b.msg = 'Your membership has been cancelled, DO NOT REPLY.'
ORDER BY a.phone
---------------------------
-- Insert Confirmation MT
---------------------------
GO
INSERT INTO DBSRVR2.Queues.dbo.Outqueue
( phone, routing, service, keyword, keyid, msg, result, origin )
SELECT a.phone, c.freeOffAggMt AS routing,
ISNULL(d.service, 0) AS service,
ISNULL(d.keyword, 0) AS keyword,
ISNULL(d.keyid, 0) AS keyid,
'Your membership has been cancelled, DO NOT REPLY.' AS msg,
61 AS result, 3 AS origin
FROM #Temp a
INNER JOIN DBSRVR1.OUTNOW.DBO.Phonelist b (read committed snapshot)
ON b.phone = a.phone
INNER JOIN DBSRVR1.OUTNOW.DBO.DefineMtRoutes c (read committed snapshot)
ON c.aggregator = b.aggregator AND
c.network = b.network
LEFT JOIN DBSRVR1.OUTNOW.DBO.Services d (read committed snapshot)
ON d.userid = b.userid AND
d.service = b.srvspref
---------------------------
-- Insert into BLOCK
---------------------------
GO
INSERT INTO DBSRVR2.QUEUES.DBO.Block
( phone, aggregator, comment )
SELECT x.phone, 0 AS aggregator, 'WASP complaint (BKM)' AS comment
-- Rowcount (for future reporting)
SELECT COUNT(*) AS total
FROM #Temp x
LEFT JOIN Block y (NOLOCK)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (y.phone IS NULL)
---------------------------
-- Check results
---------------------------
GO
UPDATE y
SET comment = 'WASP complaint (BKM)'
--
SELECT y.*
FROM #Temp x
INNER JOIN Block y (read committed snapshot)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (comment LIKE '%comment provided')
Your code is fine. See my suggested changes in bold for how to avoid repeatedly entering the phone numbers.
For making this a sproc, you might want to consider passing the phone numbers as a comma-delimited list of phone numbers which are passed to a parameter of the sproc. Use the Delimited Split Function to quickly and efficiently parse this into individual records.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 3, 2010 at 1:58 pm
Hi Wayne,
That solves the repeating phone number issue, but do you think some sort of "If, Then.." control flow is needed?
October 3, 2010 at 6:41 pm
That depends... do your requirements specify sometimes doing some of this, and at other times not doing it? If so, then it would be needed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 4, 2010 at 1:45 pm
Well, the consensus is that the final check isn't necessary, my remainings concern are error-handling, emailing that rowcount automatically, and possibly lifting the phone numbers using OpenRowset.
But otherwise, you think the code is ready to be scheduled?
October 4, 2010 at 2:23 pm
But otherwise, you think the code is ready to be scheduled?
If you have to ask that the answer is no...
October 4, 2010 at 2:30 pm
blacklabellover2003 (10/4/2010)
Well, the consensus is that the final check isn't necessary, my remainings concern are error-handling, emailing that rowcount automatically, and possibly lifting the phone numbers using OpenRowset.But otherwise, you think the code is ready to be scheduled?
Noone here can answer that question. You have the Dev environment, you have the requirements and desired result sets... you have to make that decision after thorough testing.
Launching anything untested into production would be foolish. You need to make that call, not us.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2010 at 4:02 pm
Assuming you've tested it, on real data, and it returns accurate results, and someone else verifies this, it's probably ready.
But as mentioned, you need to test it in your environment.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 5, 2010 at 6:59 am
1) you should not use UNION for the population - there are no duplicates so use UNION ALL to avoid DISTINCT operator under the covers.
2) you should CAST the phone number strings to be the exact data type of the columns you are joining to to avoid any implicit conversions that will void index seeks.
3) Do not count the rows you just put in with a SELECT statement. have a rowcnt variable available and put the @@rowcount from the INSERT into that just after the INSERT. I note the rowcount may not be available depending on the type of linked server - not sure about that.
4) I see NO error handling at all
5) curious about your the desire to put everything into one sproc
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply