Nesting SPROCS - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Nesting SPROCS

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-10-21 : 10:17:14

Hi
I have 5 update statements which i have in SPROC. i have to run the sproc for different dates

eg;)of one of the update statements
Update ascg
set ASC_CHANGES = 5
where asc_Eff_Start = '04/01/2008'
and asc_link is not null
and asc_hcpcs not in (select asc_hcpcs from ascg where asc_Eff_Start = '01/01/2008')

create procedure sp_ASCNewHCPCSpolicychange
@date1 varchar(10),
@date2 varchar(10)
as
Update ascg
set ASC_CHANGES = 5
where asc_Eff_Start = @date2
and asc_link is not null
and asc_hcpcs not in (select asc_hcpcs from ascg where asc_Eff_Start = @date1)

the dates come from the query

select distinct asc_eff_start from ASCG
where asc_eff_Start > '01/01/2007'
order by asc_Eff_Start

the dates i would be using for date1 and date2 parameters are
--2008-01-01 00:00:00
--2008-04-01 00:00:00
--2008-07-01 00:00:00
--2008-10-01 00:00:00
--2009-01-01 00:00:00
--2009-04-01 00:00:00
--2009-07-01 00:00:00
--2009-10-01 00:00:00
--2010-01-01 00:00:00
--2010-03-23 00:00:00
--2010-04-01 00:00:00
--2010-06-01 00:00:00
--2010-06-03 00:00:00
--2010-07-01 00:00:00
--2010-10-01 00:00:00

is there a way to write a nesting sproc so that i dont have to execute the sproc for every date combination. Normally i would do the following

execute procedure sp_ASCratechangepolicychange '01/01/2008', '04/01/2008'
execute procedure sp_ASCratechangepolicychange '04/01/2008', '2008/07/01'
execute procedure sp_ASCratechangepolicychange '2008/07/01', '2008/10/01'
execute procedure sp_ASCratechangepolicychange '2008/10/01', '2009/01/01'
execute procedure sp_ASCratechangepolicychange '2009/01/01', '2009/04/01'

Thanks for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-23 : 02:40:27

you mean take two dates at a time from query and then pass it in turn to @date1 and @date2?

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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-26 : 13:55:04

Why not put it all in one MERGE statement?

MERGE INTO ASCG
USING
(SELECT X.date1, X.date2, X.asc_eff_start_date
FROM (VALUES ('2008-01-01', '2008-04-01', '2007-01-01'),
('2008-07-01', '2008-10-01', '2007-01-01'),
('2009-01-01', '2009-04-01', '2007-01-01'),
('2009-07-01', '2009-10-01', '2007-01-01'),
('2010-01-01', '2010-03-23', '2007-01-01'),
('2010-04-01', '2010-06-01', '2007-01-01'),
('2010-06-03', '2010-07-01', '2007-01-01'))
AS A) X
ON X.date2
IN (SELECT A.asc_eff_start_date
FROM ASCG AS A
WHERE A.asc_eff_start_date > X.asc_eff_start_date)
AND ASCG.asc_link IS NOT NULL
AND ASCG.asc_hcpcs
NOT IN (SELECT asc_hcpcs
FROM ASCG
WHERE asc_eff_start_date = X.date1)
WHEN MATCHED
THEN UPDATE
SET asc_changes = 5;

I have an article on long parameter lists you will want to read.

http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources