May 1, 2009 at 11:59 am
Tom.Thomson (4/29/2009)
dbishop (4/14/2009)
That is the point I was making. The author assumed that by changing his code from the individual steps (procedures) to Cookies, with chocolate chips, all of a sudden it was no longer procedural. I was trying to point out the falisy of that statement.The trouble with this statement is that Barry made it absolutely clear which sense of the word "declarative" he was using. There are numerous different definitions of "declarative", but Barry was very clear: declarative in his sense means specifying the result you want (chocolate chip cookies) rather than specifying a method of achieving it (make the mix, bake it). You've fallen into the trap of saying that "the desired result is cookies with chocolate chips" means the same as "make a cookie mix and bake it to get cookies with chocolate chips" but they are clearly different: the former leaves you the choice between buying a bag of cookies, cooking your own cookies, or commissioning someone else to cook them for you (and a few other methods, of course); the latter requires you to cook them, not buy them or get someone else to make them. So the former is not at all procedural - how can it be, when it doesn't constrain the procedure for achieving the end result, it only constrains the end result?
Excellent post Tom, one of my favorite ever. I should get you to do all of my public statements when I am sick or unavailable. 🙂
However, I don't completely agree that SQL is actually declarative in Barry's sense, although a very large subset of it is. There are things that I can only express by an ordered sequence of statements - for example if I want to update several tables (unless there are some new language extensions I haven't heard about yet) - particularly if later updates depend on some characteristic of the earlier ones (total number of rows affected so far, for example).
I completely agree. In fact, I am trying to get folks to stop using the most procedural parts of SQL. And yes, although there are limited ways to do multiple output tables in 2005 (and some better ones in 2008), for the most part you still need Sequence (first of the three procedural structures) to do this, though usually there isn't even a true need for sequencing them (if only we had the legendary Simultaneous control structure).
The fact that there are procedural elements in Transact SQL is pretty evident when you consider that one of our primary code structures is called a "Stored Procedure".
Something that SQL could usefully acquire from other declarative languages is a MAP operator (to map a stored procedure or an extended stored procedure over a table where each row represents a set of parameters for it, with no defined order in the absence of an order by clause). This would be much cleaner than the current game of building a string of commands and using exec SQL on it, and on cases where the order clause was absent it would allow parallelism that isn't achievable with exec(SQL) (since teh execution of a series of SQL statements is serial, although parallelism can happen within an individual statement). It might also be good to pick up the REDUCE operator from the same school - seems rather better than the current "select @v-2 = expr involving @v-2 from ..." way of doing reduce, and may add extra flexibility (and why does @v-2 in that have to be a scalar variable? perhaps it's because tables are no "first class objects" in SQL?).
Yep, MAP would be a great way to formalize (and allow for optimization of) the dynamic SQL approach to executing a sProc for every row in a set.
REDUCE is a bit more complicated. The current pseudocursor tricks could do everything that we wanted and more if Microsoft would just A) formalize an ordering/grouping syntax, B) allow mixed data & variable SELECTS, at all levels and C) just fully support what they have already been implementing for years anyway. Heck if we could get this, then we could throw away the problematic UPDATE version and we wouldn't need a new REDUCE operator at all. Now true, pseudocursors are still fairly procedural, but they are very constrained and contained so they are only about a 7 compared to the 10 of Cursor & WHILE loops.
But there is another way that Microsoft could go here that would be even better than REDUCE or pseudocursors: Allow user-defined Aggregate (including order-sensitive) and Windowed Aggregate functions in T-SQL. You can do half of this now with CLR, however in T-SQL you could actually require declarative set-based definitions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 12:26 pm
Tom.Thomson (4/30/2009)
RBarryYoung (4/15/2009)
Just because pure set-based SQL may not be sufficient for a particular task does not mean that that justifies Cursors or loops in SQL. There are still a lot of miles between the two and a lot of other options that are not even on the same scale.Often the best solution is a loop -
Tom: You take issue with me on several points in this post, but I am not convinced that we actually differ on most of them. For instance:
... in fact I can't see a way of doing batching/chunking with delays between batches without using a loop.
Here's the strongest statement that I make on this from the article:
Let me say that again so that there is no uncertainty: As of SQL Server 2005, the only reason to use Cursors is if you actually want to slow down your code (for instance to do a big update in chunks).
I have no objection to using Cursors or While loops for chunking because the purpose of chunking is to slow down a large operation so that it does not impede the rest of the system.
Of course, as you next imply, Cursors and While loops are not the only way to do that:
I usually express my loop as a schedule in MSDB, and I usualy use SQL to create that schedule rather than going through the job creation wizard, since the job may have to be created at every customer site and running some SQL is a lot less time consuming than using the wizard. Would it be reasonable to claim that I'm not writing loops in SQL? Maybe, but a loop is what I want for my solution and I'm telling the SQL system what loop I want, so if SQL as a declarative language that's writing a loop in SQL.
I've got a whole section in Part 2[/url] about this:
[h3]What Do I Really Mean By "Cursors and Loops"?[/h3]
One thing that I should make clear before we go any further is just exactly what do I mean by "Cursors" and "Loops" in SQL? Technically, anything in SQL that serializes a data stream and can keep positional context is a cursor. However, when I say "cursors are bad" I do not mean this more general designation of cursors, such as client-side cursors, internal cursors, implicit cursors, pseudo-cursors, etc. I mean explicit Transact-SQL server-side cursors. These are the kind that are explicitly written in procedures and that use the CURSOR datatype. You may have noticed that I usually capitalize "Cursor" and this is to intentionally indicate these explicit Cursors. In the event that I should ever be talking about the more general concept or types of cursors, then I use the lower case form.
Loops also exist at all levels of SQL Server and any processing of a set by a processor must at some level come down to one or more loops. However, my concern is with explicit loops, particularly the WHILE statement in SQL. Implicit loops and cursors are of no real concern in this for two reasons. First, they are implemented by SQL Server below the statement level and are thus as efficient as anything else in SQL Server. And secondly, their use by SQL Server is technically a procedural implementation of the declarative SQL commands that we have given it, which is entirely what is expected of a declarative programming environment.
So I am only arguing for dropping explicit Cursors and While loops.
Finally, you say:
I'm being a bit pedantic here, I know, but I,m doing it because think your over-strong denial of the usefulness of loops detracts from your message.
Now if instead of loops you had said "while loops" that would be a different story, of course (I use those too sometimes, in SQL 2000, but only because 8000 isn't big enough no need in SQL 2005 and onwards).
I accept your criticism on this point. "While loops" is what I intended and what I should have said. I will try to be more conscientious about this in the future (Jeff Moden actually pokes me about this too).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 12:29 pm
Edit: spurious post. No one has replied to it yet, but I still cannot delete it, so I have erased it instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 1, 2009 at 1:23 pm
... declarative in his sense means specifying the result you want (chocolate chip cookies) rather than specifying a method of achieving it (make the mix, bake it). You've fallen into the trap of saying that "the desired result is cookies with chocolate chips" means the same as "make a cookie mix and bake it to get cookies with chocolate chips" but they are clearly different: the former leaves you the choice between buying a bag of cookies, cooking your own cookies, or commissioning someone else to cook them for you (and a few other methods, of course); the latter requires you to cook them, not buy them or get someone else to make them. So the former is not at all procedural - how can it be, when it doesn't constrain the procedure for achieving the end result, it only constrains the end result?
A very clear way of going back to basics.
That is nice too.
June 25, 2010 at 12:58 am
I have seen a number of articles recently on moving away from cursors and fully understand the performance benefits in doing so.
That said, I use cursors as I need to get data, manipulate it and then send specific Emails based on that manipulated data. At the moment I cannot see a way of doing this in SQL other than by using cursors.
An article on this particular aspect would be most welcome to me and I suspect a large number of others.
Thank you in advance.
Colin
June 25, 2010 at 1:35 am
I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).
The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.
(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp]') AND type in (N'U'))
DROP TABLE [dbo].[Temp]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp2]') AND type in (N'U'))
DROP TABLE [dbo].[Temp2]
GO
/****** Object: Table [dbo].[Temp] Script Date: 10/28/2009 08:20:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temp](
[ObjectId] [int] NOT NULL,
[ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Temp]
([ObjectId], [ObjectName])
SELECT 1, 'Parent 1' UNION ALL
SELECT 2, 'Parent 2' UNION ALL
SELECT 3, 'Parent 3' UNION ALL
SELECT 4, 'Parent 4'
CREATE TABLE [dbo].[Temp2](
[ObjectId] [int] NOT NULL,
[ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ParentId] [int] NOT NULL,
[ChildId] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Temp2]
([ObjectId], [ObjectName], [ParentId], [ChildId])
SELECT 1, 'Option 1',1, 10 UNION ALL
SELECT 1, 'Option 1',2,20 UNION ALL
SELECT 1, 'Option 1',3,30 UNION ALL
SELECT 1, 'Option 1',4,40 UNION ALL
SELECT 1, 'Option 2',1,11 UNION ALL
SELECT 1, 'Option 2',2,21 UNION ALL
SELECT 1, 'Option 2',3,31 UNION ALL
SELECT 1, 'Option 2',4,41
-- FROM: http://www.sqlservercentral.com/articles/T-SQL/63681/
SELECT ObjectId, ObjectName, --(4)
[1] AS [Parent 1], --(3)
[2] AS [Parent 2],
[3] AS [Parent 3],
[4] AS [Parent 4]
FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src --(1)
PIVOT (Min(ChildId) FOR ParentId IN ([1],[2],[3],[4])) AS pvt --(2)
ORDER BY ObjectName
DECLARE @ObjectId int
DECLARE @ObjectName varchar(25)
DECLARE @NewColumnName varchar(8000)
DECLARE @NewColumnValue varchar(8000)
DECLARE @NewSQL varchar(8000)
DECLARE Object_cursor CURSOR FOR
SELECT ObjectId, ObjectName FROM Temp FOR READ ONLY
OPEN Object_cursor
FETCH FROM Object_cursor INTO @ObjectId, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@NewColumnName IS NULL)
BEGIN
SET @NewColumnName = '[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']'
SET @NewColumnValue = '[' + CAST(@ObjectId as varchar(5)) + ']'
END
ELSE
BEGIN
SET @NewColumnName = @NewColumnName + ',[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']'
SET @NewColumnValue = @NewColumnValue + ',[' + CAST(@ObjectId as varchar(5)) + ']'
END
FETCH NEXT FROM Object_cursor INTO @ObjectId, @ObjectName
END
CLOSE Object_cursor
DEALLOCATE Object_cursor
SET @NewSQL = 'SELECT ObjectId, ObjectName, ' + @NewColumnName +
' FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src
PIVOT (Min(ChildId) FOR ParentId IN (' + @NewColumnValue + ')) AS pvt ORDER BY ObjectName'
EXEC (@NewSQL)
June 25, 2010 at 3:05 am
The topic of the series is interesting and timely, but the writing style in the introduction is goofy. It could have been replaced by a single sentence:"Cursors are slow and resource intensive, but there is a better way."
June 25, 2010 at 5:50 am
I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....
I'm really looking for a solution to the following:-
1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?
I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...
Sadly, logistics and transfer speeds make the option of external processing not possible.
I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.
June 25, 2010 at 6:44 am
I would have one suggestion in your article, you shoudl close and deallocate the cursor. People who use these types of articles to learn new things can learn really bad habits if the examples don't have proper memory and resource usage.
Thank you, and keep the faith! I think a few early posters got up on the wrong side of the bed this AM.
June 25, 2010 at 7:38 am
Michael Jenck (6/25/2010)
I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.
(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp]') AND type in (N'U'))
DROP TABLE [dbo].[Temp]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp2]') AND type in (N'U'))
DROP TABLE [dbo].[Temp2]
GO
/****** Object: Table [dbo].[Temp] Script Date: 10/28/2009 08:20:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Temp](
[ObjectId] [int] NOT NULL,
[ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Temp]
([ObjectId], [ObjectName])
SELECT 1, 'Parent 1' UNION ALL
SELECT 2, 'Parent 2' UNION ALL
SELECT 3, 'Parent 3' UNION ALL
SELECT 4, 'Parent 4'
CREATE TABLE [dbo].[Temp2](
[ObjectId] [int] NOT NULL,
[ObjectName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ParentId] [int] NOT NULL,
[ChildId] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Temp2]
([ObjectId], [ObjectName], [ParentId], [ChildId])
SELECT 1, 'Option 1',1, 10 UNION ALL
SELECT 1, 'Option 1',2,20 UNION ALL
SELECT 1, 'Option 1',3,30 UNION ALL
SELECT 1, 'Option 1',4,40 UNION ALL
SELECT 1, 'Option 2',1,11 UNION ALL
SELECT 1, 'Option 2',2,21 UNION ALL
SELECT 1, 'Option 2',3,31 UNION ALL
SELECT 1, 'Option 2',4,41
-- FROM: http://www.sqlservercentral.com/articles/T-SQL/63681/
SELECT ObjectId, ObjectName, --(4)
[1] AS [Parent 1], --(3)
[2] AS [Parent 2],
[3] AS [Parent 3],
[4] AS [Parent 4]
FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src --(1)
PIVOT (Min(ChildId) FOR ParentId IN ([1],[2],[3],[4])) AS pvt --(2)
ORDER BY ObjectName
DECLARE @ObjectId int
DECLARE @ObjectName varchar(25)
DECLARE @NewColumnName varchar(8000)
DECLARE @NewColumnValue varchar(8000)
DECLARE @NewSQL varchar(8000)
DECLARE Object_cursor CURSOR FOR
SELECT ObjectId, ObjectName FROM Temp FOR READ ONLY
OPEN Object_cursor
FETCH FROM Object_cursor INTO @ObjectId, @ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@NewColumnName IS NULL)
BEGIN
SET @NewColumnName = '[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']'
SET @NewColumnValue = '[' + CAST(@ObjectId as varchar(5)) + ']'
END
ELSE
BEGIN
SET @NewColumnName = @NewColumnName + ',[' + CAST(@ObjectId as varchar(5)) + '] AS [' + @ObjectName + ']'
SET @NewColumnValue = @NewColumnValue + ',[' + CAST(@ObjectId as varchar(5)) + ']'
END
FETCH NEXT FROM Object_cursor INTO @ObjectId, @ObjectName
END
CLOSE Object_cursor
DEALLOCATE Object_cursor
SET @NewSQL = 'SELECT ObjectId, ObjectName, ' + @NewColumnName +
' FROM (SELECT ObjectId, ObjectName, ParentId, ChildId FROM dbo.Temp2 WHERE (ObjectId = 1)) AS src
PIVOT (Min(ChildId) FOR ParentId IN (' + @NewColumnValue + ')) AS pvt ORDER BY ObjectName'
EXEC (@NewSQL)
You've already cited one article to get you started in your code. Here's part 2 which should help you get over the hump on such a dynamic crosstab...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2010 at 7:38 am
Nick Walton (6/25/2010)
I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-
1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?
I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...
Sadly, logistics and transfer speeds make the option of external processing not possible.
I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.
There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.
June 25, 2010 at 7:46 am
Michael Jenck (6/25/2010)
I have been trying to figure out a way to eliminate the following cursor. I figure this would be a good place to post this seeing the title of this article :-).The only conditions are that I don't know in advance what the child and parent records are - just that at least 1 of each exists.
(Note: I'm not a DBA. I fall in the category of knowing just enough SQL to be dangerous.)
<snip>
When you hit a problem in T-SQL that requires a cumbersome solution, you should sit back and consider whether it ought to be done in T-SQL in the first place. The SQL language is fundamentally not designed for dynamic column generation. Further, T-SQL was not designed for fancy string manipulation. So, the solution to eliminating a cursor in this case is to not do dynamic crosstabs in T-SQL. Build your query in a middle-tier component or reporting tool. Is it possible to do it in T-SQL? Yes, just like it is possible to use Excel as a database or to write memos. That does not mean you should use it for this purpose just as you shouldn't use T-SQL for dynamic crosstabs.
June 25, 2010 at 7:53 am
Thomas-282729 (6/25/2010)
Nick Walton (6/25/2010)
I'm really looking forward to this series, as there's been a higher than average number of "Cursors are baaaaaad" posts on SSC recently. Your article seems to indicate that cursors can be eliminated entirely....I'm really looking for a solution to the following:-
1. How do I execute a stored procedure for each row in a table (temporary or otherwise), which does some very complex calculations (using other tables) and then updates (or inserts if it doesn't already exist) a row in another table (a common operation for month-end tasks, for example), or if spooling out to external consumers, such as Broker peers?
I don't believe SQL Server 2005 allows a) an sp to be executed per-row or b) a function to update a table...
Sadly, logistics and transfer speeds make the option of external processing not possible.
I hope that one of your articles covers this situation, as I'd love to get rid of all these cursors.
There is an answer, but you probably won't like it: don't do it in a stored procedure. If you cannot accomplish a given piece of work in a set-based format, it was probably not meant to be done in a database. Stored procedures were not meant to encapsulate complex business logic of the type you suggest. Databases are first and foremost meant to serve up data. Further, if there is so much data that streaming it to an external service is impractical, then I would suggest that it is also impractical to do it row-by-row in a stored proc. If you can do it row-by-row in a proc, then you can do it row-by-row in an external service.
That's also where a "control loop" comes into play and "control loops" are one of the very few places where a CURSOR or WHILE LOOP are generally ok to use.
As for not using stored procedures to execute complex business logic goes, I guess I'll just have to agree to disagree with you on that. It's usually much more effective to process the data at the source than to stream millions of rows over the pipe.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2010 at 7:55 am
Hi,
I come from an RPG/DB2 background so I am sort of used to looping through records in order to do something, but I haven't actually used a cursor yet. Does using a Script Transformation in SSIS count? Does someone have an example of a problem that could only be solved with a cursor? Perhaps Mr. Neufeld can provide one?
I have to agree that an example of an improper usage of a cursor would have been nice, even if the resolution was put into part 2. Especially since the provided example doesn't do anything but count rows. Would someone with any SQL knowledge at all write something like that? (umm, never mind - I just read part 2 and it seems that some folks might; one of the examples there uses a cursor for a simple concatenation and the author tells us that it was based on an actual code sample - that one would have been a better homework example, though)
Oh, By the way -
Select COUNT(1) * COUNT(2) From master.sys.columns
June 25, 2010 at 8:00 am
mlabedz (6/25/2010)
I think a few early posters got up on the wrong side of the bed this AM.
At 35 pages by 10am GMT, I think that qualifies as Understatement Of The Year(tm) :*)
Perhaps a way to thank all the people who weren't too rude...
DECLARE @ThanksTo nvarchar(512)
DECLARE @numPosts int
DECLARE C AS CURSOR LOCAL FAST_FORWARD FOR
SELECT [Author_Email] FROM [dbo].[ArticleDiscussionComments] WITH (NOLOCK)
WHERE [dbo].[fn_isNotDisparaging]([BodyText])
AND [dbo].[fn_seemToThinkCursorsAreAllBad]([BodyText])
AND replyDate IS NULL;
OPEN C
BEGIN TRY
FETCH NEXT FROM C INTO @ThanksTo
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @numPosts = COUNT(*) FROM Article WHERE Author_Email = @ThanksTo
SET @thanks = ' I see you''ve posted ' + LTRIM(RTRIM(STR(@numPosts))) + ' times before, '
IF (@numPosts > 100)
SET @thanks = @thanks + 'I hope I can change your mind. Thank you.'
ELSE
SET @thanks = @thanks +'I hope my posts are instructive, young padawan. Thank you.'
EXEC msdb.dbo.sp_send_dbmail @recipients=@Author,
@subject = 'Thanks',
@body = @thanks;
FETCH NEXT FROM C INTO @ThanksTo
END
UPDATE ArticleDiscussionComments WITH (NOLOCK)
SET ReplyDate = getdate()
WHERE dbo.fn_isNotDisparaging([BodyText])
AND [dbo].[fn_seemToThinkCursorsAreAllBad]([BodyText])
AND replyDate IS NULL;
END TRY
BEGIN CATCH
exec sp_logError('An error has occurred replying to the nice people');
exec sp_logError(ERROR_MESSAGE())
END CATCH
CLOSE C
DEALLOCATE C
Viewing 15 posts - 331 through 345 (of 380 total)
You must be logged in to reply to this topic. Login to reply