January 16, 2012 at 5:46 pm
I need some guidance on what is a good construct/method of running a stored procedure many times by iterating through a temp table for it's imput parameters?
Any help is appreciated.
Thanks
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
January 16, 2012 at 8:56 pm
If the Temp Table has sequential numbering like that offered by an IDENTITY column and you've done nothing to disturb the sequence of numbers, then you could use a counter and WHILE loop to read each row into a variable and then exec the proc with the variable as a parameter to the proc.
You could also use a cursor to do the same thing whether you have sequential numbering or not but you need to populate the cursor in the order that you wish to execute in.
Last but not least, you could build a wad of concatenated SQL from your table to build all of the Execute commands as a single variable and then execute the variable.
I will say, however, that except for sending email and doing things across multiple databases, it's usually a bad idea to have a stored procedure that handles only one row at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2012 at 8:33 am
Hi,
I sometimes like to construct the code to execute the procedures with a script like the following, it has been very useful to me lot of times, but it has the disadvantage that you cannot do very much of error handling.
-- Populate the parameters table. This step is not needed, since you alredy your temp table
DECLARE @ParametersTable AS TABLE (Parameter1 VARCHAR(100),Parameter2 VARCHAR(100))
INSERT INTO @ParametersTable
SELECT 'Adrian','Facio' UNION ALL
SELECT 'Karina','Barreras'
-- Create the code to execute the procedure many times
DECLARE @ExecuteProcedures AS VARCHAR(MAX)
SET @ExecuteProcedures = CONVERT ( VARCHAR(MAX),
(SELECT 'EXEC TheProducedureName '''+Parameter1+''','''+Parameter2+''' '
AS 'text()'
FROM @ParametersTable -- Your temp table
FOR XML PATH ('')
)
)
PRINT @ExecuteProcedures
EXEC (@ExecuteProcedures)
January 18, 2012 at 5:19 am
Jeff Moden (1/16/2012)
If the Temp Table has sequential numbering like that offered by an IDENTITY column and you've done nothing to disturb the sequence of numbers, then you could use a counter and WHILE loop to read each row into a variable and then exec the proc with the variable as a parameter to the proc.
If your temp table does not yet have sequential numbering, you may want to add this to your definition:
selectyour_col1, your_col2, ... , your_coln
--here's the important part to be added
, row_number() over(order by someColumn) as rowcnt
into #TempTable
from YourTable
In the following while loop, you'd just update column rowcnt:
while (
select count(*)
from #TempTable
where rowcnt = 1) = 1
begin
--here's your original code
--...
--to step through the temp table, add the following
update #TempTable
set rowcnt = rowcnt - 1
if (
select count(*)
from #TempTable
where rowcnt = 1) = 0
BREAK
else
CONTINUE
end
--you may want to add a message indicating you're done
print 'Done running yourStoredProcedure.';
Depending on how many rows your temp table has, check it out in a test environment to find out about the burden on your system.
-Michael
PS: Edited a typo.
January 18, 2012 at 5:29 am
adrian.facio (1/17/2012)
Hi,I sometimes like to construct the code to execute the procedures with a script like the following, it has been very useful to me lot of times, but it has the disadvantage that you cannot do very much of error handling.
-- Populate the parameters table. This step is not needed, since you alredy your temp table
DECLARE @ParametersTable AS TABLE (Parameter1 VARCHAR(100),Parameter2 VARCHAR(100))
INSERT INTO @ParametersTable
SELECT 'Adrian','Facio' UNION ALL
SELECT 'Karina','Barreras'
-- Create the code to execute the procedure many times
DECLARE @ExecuteProcedures AS VARCHAR(MAX)
SET @ExecuteProcedures = CONVERT ( VARCHAR(MAX),
(SELECT 'EXEC TheProducedureName '''+Parameter1+''','''+Parameter2+''' '
AS 'text()'
FROM @ParametersTable -- Your temp table
FOR XML PATH ('')
)
)
PRINT @ExecuteProcedures
EXEC (@ExecuteProcedures)
That won't work because you're missing a semi-colon to break up your batched statements. Try this: -
-- Populate the parameters table. This step is not needed, since you alredy your temp table
DECLARE @ParametersTable AS TABLE (Parameter1 VARCHAR(100),Parameter2 VARCHAR(100))
INSERT INTO @ParametersTable
SELECT 'Adrian','Facio' UNION ALL
SELECT 'Karina','Barreras'
-- Create the code to execute the procedure many times
DECLARE @ExecuteProcedures AS VARCHAR(MAX)
SELECT @ExecuteProcedures = tblsTxt.sqlCode
FROM (SELECT '; EXEC TheProducedureName '''+Parameter1+''','''+Parameter2+''''
FROM @ParametersTable -- Your temp table
FOR XML PATH(''), TYPE) tbls(sqlCode)
CROSS APPLY (SELECT STUFF(tbls.sqlCode.value('./text()[1]', 'VARCHAR(MAX)'),1,2,'')) tblsTxt(sqlCode)
PRINT @ExecuteProcedures
EXEC (@ExecuteProcedures)
January 18, 2012 at 9:09 am
Did you tried it before your change??
January 18, 2012 at 9:14 am
Another, usually better, option is break open the procedure and turn it into an operation that can take a whole table of "parameters" and work on them in a set-based method.
Takes some work to set up, but is almost always worth it in the long run.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 9:44 am
adrian.facio (1/18/2012)
Did you tried it before your change??
Admittedly, the answer to that is "no".
However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.
January 18, 2012 at 9:55 am
Cadavre (1/18/2012)
adrian.facio (1/18/2012)
Did you tried it before your change??Admittedly, the answer to that is "no".
However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.
I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.
They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.
Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 10:06 am
well it works on my computer, but i agree that adding semicolons will make it better
January 18, 2012 at 10:06 am
GSquared (1/18/2012)
Cadavre (1/18/2012)
adrian.facio (1/18/2012)
Did you tried it before your change??Admittedly, the answer to that is "no".
However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.
I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.
They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.
Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.
I'll happily agree to disagree in this case I think.
Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.
I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work 😀
January 18, 2012 at 1:53 pm
Cadavre (1/18/2012)
GSquared (1/18/2012)
Cadavre (1/18/2012)
adrian.facio (1/18/2012)
Did you tried it before your change??Admittedly, the answer to that is "no".
However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.
I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.
They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.
Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.
I'll happily agree to disagree in this case I think.
Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.
I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work 😀
Ok... now you're getting spooky. Are you sure you're not a long lost brother or something?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 18, 2012 at 8:43 pm
Cadavre (1/18/2012)
Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work 😀
For me, it's simple: after a fairly short acclimatisation period in a team I run you will be expected to insert char(13)+char(10) in strings of sql to make printed versions readable; if you don't, you will be reminded; if reminders don't work, bye-bye (that never happened, people that stupid don't get into the team in the first place). It's not just a preference any more than decent code layout is an just a preference in direct code (as opposed to executable strings) - that is, it's not an option at all, it's a mandatory requirement.
Incidentally, using char(10)+char(13) (as you apparently do) instead is not acceptable - some people are still sometimes stuck with printing on old-fashioned typerwriter-style printers where that results in a horrible mess (I thank the powers that be that I haven't seen wide carriage printers where you might need char(13)+char(10)+char(0) to get it right since long before I got into SQL).
Tom
January 19, 2012 at 2:05 am
L' Eomot Inversé (1/18/2012)
Incidentally, using char(10)+char(13) (as you apparently do) instead is not acceptable
I more meant that I include CR/LF, I didn't mean that I specifically wrote them as LF + CR but 10 comes before 13 so when writing in English I tend to write it down that way around (unless specifically asking one of the team to include them in some dynamic SQL).
Jeff Moden (1/18/2012)
Ok... now you're getting spooky. Are you sure you're not a long lost brother or something?
Not sure if I could possibly have got a better compliment! I'm nowhere near up to your standards yet, but hopefully one day 😉
January 19, 2012 at 7:07 am
Cadavre (1/18/2012)
GSquared (1/18/2012)
Cadavre (1/18/2012)
adrian.facio (1/18/2012)
Did you tried it before your change??Admittedly, the answer to that is "no".
However IMO, to avoid ambiguity, whether or not your method works is irrelevant. It needs a semi colon appended to the end of each EXEC statement to show where each batch ends. This will also help to "future proof" the code.
I put semicolons at the end of my SQL statements for those exact reasons, but I disagree with your statement.
They aren't needed, the dynamically generated statement isn't meant for human-eye consumption (no need to show where they end), and current versions of the SQL parser/compiler don't need them except in a few cases.
Would I have included them in a dynamic script I wrote? Yes. But saying it's "wrong" to not have them and "whether or not your method works is irrelevant", is a bit strong in this case.
I'll happily agree to disagree in this case I think.
Although the dynamicSQL is not meant for human consumption, when digging into older code it's always helpful to add PRINT to see what has been generated. Without the semi-colons (and the CHAR(10) + CHAR(13) that I normally include, although that really is just preference) it can be extremely difficult to read.
I will agree that saying it is "wrong" is a bit strong, but even so I'd insist on it being put "right" where I work 😀
I must not have written clearly.
I'm not disagreeing with semicolons being an improvement. I'm disagreeing with the tennor of the statement you made. Or should I reword that to, "You're wrong, manners and respect are needed, and help to future-proof you against ... well ... something or other ... blah blah"? (Over the top for humor, not to offend, but to make a bit of a point.)
All I'm saying is a bit more tact would have been good.
Though I do also disagree somewhat with the use of "needed" in that statement. They aren't a necessity. A strong preference, and one that I share, but not a necessity. Syntactically correct T-SQL is a necessity. Formatting, readability, et al, are important, but not necessary. By definition of necessary/needed/necessity. But that's my editorial opinion on the use of the word, not a statement of incontrovertible fact.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply