March 23, 2012 at 8:29 am
Hi,
Is there a syntax that will allow me to execute a stored procedure for each row of a table (with or without a where clause) ?
Something like a select, but an exec instead : EXEC sp t.C1 FROM Table t
The only method to do it that i know of is to use a cursor (or a loop), but is there another way ?
If EXISTS (SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID(N'[tempdb]..#tData') AND type='U')
DROP TABLE #tData
CREATE TABLE #tData
(Country_CD NVARCHAR(6) collate database_default Not NULL
,YYYYNVARCHAR(4) collate database_default Not NULL
,MMNVARCHAR(2) collate database_default Not NULL
,DDNVARCHAR(2) collate database_default Not NULL
,DescriptionNVARCHAR(250) collate database_default
)
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'01',N'01',N'Jour de l''an')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'04',N'06',N'Vendredi Saint')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'04',N'09',N'Lundi pâques')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'05',N'01',N'Fête du travail')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'05',N'08',N'Fête de la victoire')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'05',N'17',N'Jour de l''ascension')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'05',N'28',N'Pentecôte')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'07',N'14',N'14 juillet')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'08',N'15',N'Assomption')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'11',N'01',N'La toussaint')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'11',N'11',N'Jour de l''armistice')
INSERT INTO #tData (Country_CD,YYYY,MM,DD,Description) VALUES (N'FR',N'2012',N'12',N'25',N'Noêl')
DECLARE @Country_CD NVARCHAR(6)
,@DayOffDATETIME
,@DescriptionNVARCHAR(250)
DECLARE Cur CURSOR
FOR SELECT Country_CD,CONVERT(DATETIME, YYYY+MM+DD) As DayOff,Description FROM #tData
OPEN Cur
FETCH NEXT FROM Cur INTO @Country_CD,@DayOff,@Description
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC SP_MngCountryDayOff @Country_CD,@DayOff,@Description
FETCH NEXT FROM Cur INTO @Country_CD,@DayOff,@Description
END
CLOSE Cur
Thanks.
--
jpv
March 23, 2012 at 9:04 am
It may be possible to re-design your stored proc in such way that it can be executed for the whole table (or required dataset)...
If not, cursors in T-SQL are mainly designed for these sort of scenarios...
March 23, 2012 at 9:08 am
I'd want to know what the stored procedure is doing that it needs to work row by agonizing row? Think how long it would take if you needed to do this against a 1,000,000 row table at one row per second?
March 23, 2012 at 9:09 am
March 23, 2012 at 9:39 am
The SP does something very simple :
- Delete existing in a ref table.
- Insert New in that same ref table.
It's easy to do in two requests, but i'm forced to use that SP for different reasons :sick:
I'm fairly resigned to use a cursor but I wanted to do something more interesting. 😛
--
jpv
March 23, 2012 at 10:04 am
jp.v (3/23/2012)
The SP does something very simple :- Delete existing in a ref table.
- Insert New in that same ref table.
It's easy to do in two requests, but i'm forced to use that SP for different reasons :sick:
I'm fairly resigned to use a cursor but I wanted to do something more interesting. 😛
Instead of saying that it's doing something simple, it would be simpler to post stored proc code...
I wonder what reasons forced you to use this SP?
Direct order from company CEO or country constitution? :hehe:
You're always free to do something more interesting, especially on a Friday evening... 😀
March 23, 2012 at 10:09 am
jp.v (3/23/2012)
The SP does something very simple :- Delete existing in a ref table.
- Insert New in that same ref table.
It's easy to do in two requests, but i'm forced to use that SP for different reasons :sick:
I'm fairly resigned to use a cursor but I wanted to do something more interesting. 😛
Unfortunately, we aren't mind readers. You want help, show us the code, provide us with the DDL (CREATE TABLE statement) for the table(s) involved, provide sample data (a series of INSERT INTO statements) for the table(s), and the expected results based on the sample data.
March 23, 2012 at 10:24 am
Eugene Elutin (3/23/2012)
Instead of saying that it's doing something simple, it would be simpler to post stored proc code...I wonder what reasons forced you to use this SP?
Direct order from company CEO or country constitution? :hehe:
You're always free to do something more interesting, especially on a Friday evening... 😀
That's exactly it :crying: it's an order from my boss :crying:
Here is what the SP does :
DELETE FROM REF_COUNTRY_DAY_OFF
WHERE COUNTRY_CD = @countryCd AND DAY_DT = @dayDt
INSERT INTO REF_COUNTRY_DAY_OFF (country_cd, day_dt, day_ds)
VALUES(@country_cd, @day_dt, @day_ds)
And some error management. See, nothing fancy 😉
--
jpv
March 23, 2012 at 10:28 am
jp.v (3/23/2012)
Eugene Elutin (3/23/2012)
Instead of saying that it's doing something simple, it would be simpler to post stored proc code...I wonder what reasons forced you to use this SP?
Direct order from company CEO or country constitution? :hehe:
You're always free to do something more interesting, especially on a Friday evening... 😀
That's exactly it :crying: it's an order from my boss :crying:
Here is what the SP does :
DELETE FROM REF_COUNTRY_DAY_OFF
WHERE COUNTRY_CD = @countryCd AND DAY_DT = @dayDt
INSERT INTO REF_COUNTRY_DAY_OFF (country_cd, day_dt, day_ds)
VALUES(@country_cd, @day_dt, @day_ds)
And some error management. See, nothing fancy 😉
Looks to me like a simple update would work. How about giving us the info requested? We just may be able to turn this into a very simple set-based solution.
March 23, 2012 at 10:34 am
jp.v (3/23/2012)
Eugene Elutin (3/23/2012)
Instead of saying that it's doing something simple, it would be simpler to post stored proc code...I wonder what reasons forced you to use this SP?
Direct order from company CEO or country constitution? :hehe:
You're always free to do something more interesting, especially on a Friday evening... 😀
That's exactly it :crying: it's an order from my boss :crying:
Here is what the SP does :
DELETE FROM REF_COUNTRY_DAY_OFF
WHERE COUNTRY_CD = @countryCd AND DAY_DT = @dayDt
INSERT INTO REF_COUNTRY_DAY_OFF (country_cd, day_dt, day_ds)
VALUES(@country_cd, @day_dt, @day_ds)
And some error management. See, nothing fancy 😉
So, you've told your boss that you can do it in such a way that it will run 1000 times faster, be more robust and maintainable, but he said:
- "NO, you must use this SP, it's left to me by my grandma and it's very dear to me, I always use it so will you!"
I don't know why, but can't believe you...
:hehe:
March 23, 2012 at 10:41 am
Here's the proc :
CREATE PROC dbo.SP_MngCountryDayOff
(@country_cd CD
,@day_dt DT
,@day_ds DS
)
AS
/********* Variables declaration *********/
/********* Variables initialisation *********/
/* checking parameters */
/**** Treatement if no errors ******/
DELETE FROM REF_COUNTRY_DAY_OFF
WHERE COUNTRY_CD = @countryCd AND DAY_DT = @dayDt
INSERT INTO REF_COUNTRY_DAY_OFF (country_cd, day_dt, day_ds)
VALUES(@country_cd, @day_dt, @day_ds)
/***************** Error management *******************/
GO
The worst part, is that even my boss knows there's a better way.
It's just company protocol 🙁
--
jpv
March 23, 2012 at 10:56 am
Your procedure will delete and reinsert existing records (basically an update to the existing record). If the DELETE fails, no record currently exists, INSERT a new one.
The following code will do the same thing but in a set-based fashion.
with DaysOff as (
select
Country_CD,
CONVERT(DATETIME, YYYY+MM+DD) As DayOff,
Description
from
#tData -- replace this with the actual source table
)
update rcdo set -- update existing records
day_ds = do.Description
from
REF_COUNTRY_DAY_OFF rcdo
inner join DaysOff do
on (rcdo.country_cd = do.Country_CD and
rcdo.day_dt = do.DayOff);
with DaysOff as (
select
Country_CD,
CONVERT(DATETIME, YYYY+MM+DD) As DayOff,
Description
from
#tData -- replace this with the actual source table
)
insert into REF_COUNTRY_DAY_OFF (country_cd, day_dt, day_ds) -- insert new records
select do.Country_CD, do.DayOff, do.Description
from
REF_COUNTRY_DAY_OFF rcdo
right outer join DaysOff do
on (rcdo.country_cd = do.Country_CD and
rcdo.day_dt = do.DayOff)
where
rcdo.country_cd is null;
When you move to SQL Server 2008 you will be able to change this to use the MERGE statement.
March 23, 2012 at 11:02 am
...
The worst part, is that even my boss knows there's a better way.
It's just company protocol 🙁
I know couple of good ones, who help companies going to administration.
Company protocol stating how to write s**t code? I think you're mistaken.
It may be state few requirements about error logging/handling, security etc. But, it CANNOT outline, that it will be achieved by bad design practice or wrong way of coding.
Until... see my first statement. (KPMG has some free-bandwidth right now :-D)
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply