How would you create this stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How would you create this stored procedure?

Author

Topic

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-03 : 10:24:35

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')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2010-10-03 : 10:49:16

If you're on SQL 2008 use Table Valued Parameters for input param.
If not use then your sproc can accept the phone numbers in CSV form in one string.
Then split them using this method: http://www.sqlservercentral.com/articles/T-SQL/62867/
With that you have a table variable that you can join to instead of always writing select ... union...
if you must use select ... union... then use union all instead of union because union removes duplicates
while union all doesn't. this is probably an extra step you don't need.

If possible use snapshot isolation level so you won't need to use NOLOCK hints which can return you wrong results.

For the last update you can use the OUTPUT clause (http://msdn.microsoft.com/en-us/library/ms177564.aspx) to insert the updated results into a temp table and check them there.
This way you won't have to select from your tables again.
Also are you sure you need to updat the whole Block table?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.9 out!

SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 09:52:43

here's an example of how to use table valued params

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources