Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««56789

Cursors for T-SQL Beginners Expand / Collapse
Author
Message
Posted Tuesday, May 31, 2011 8:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:04 AM
Points: 3,675, Visits: 72,433
Mhlewis (5/27/2011)
We've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail.



This is a bit rough... without tables, I just wrote the basic gist here.
But, this will generate a huge dynamic sql statement that will send all the emails at once.

DECLARE @SQL nvarchar(max)

SELECT
@SQL = COALESCE(@SQL + N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''',N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''')
FROM
joblisttable

EXEC sp_executesql @SQL





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1117477
Posted Tuesday, May 31, 2011 1:55 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:59 AM
Points: 897, Visits: 1,488
Mad Myche (5/31/2011)
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.


I was just pointing that DML isn't related to modification only.
The way I see it manipulation is not same as modification.
I've heard people believing SELECT isn't part of DML because of the "modification" word.

Selecting data is a form of manipulating data unless I'm mistaken on the concept.

Best regards,



Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1117726
Posted Tuesday, May 31, 2011 2:33 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:16 AM
Points: 1,035, Visits: 410
codebyo (5/31/2011)
Mad Myche (5/31/2011)
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.


I was just pointing that DML isn't related to modification only.
The way I see it manipulation is not same as modification.
I've heard people believing SELECT isn't part of DML because of the "modification" word.

Selecting data is a form of manipulating data unless I'm mistaken on the concept.

Best regards,



You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.



/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #1117741
Posted Tuesday, May 31, 2011 6:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:34 PM
Points: 15, Visits: 103
Jeff Moden (1/1/2009)
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.

DECLARE @myVar varchar(MAX)
SET @myVar = ''

SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable

SELECT @myVar


The author also pointed that out in the article.


Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.

I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?
Post #1117806
Posted Tuesday, May 31, 2011 8:38 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:59 AM
Points: 897, Visits: 1,488
DCPeterson (5/31/2011)
You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.


Thank you for verifying the statement. I became unsure for an instant.
I have so much to learn and so little (precious) time.

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1117835
Posted Sunday, June 5, 2011 10:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 36,995, Visits: 31,519
jmaloney-1117255 (5/31/2011)
Jeff Moden (1/1/2009)
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.

DECLARE @myVar varchar(MAX)
SET @myVar = ''

SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable

SELECT @myVar


The author also pointed that out in the article.


Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.

I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?


Apologies for the late response.

Think of it as a "set based loop"... something that some of us call a "Pseudo Cursor". For every row in the table, it will add "mycolumn" from the table. So, if the table had the first three letters of the English Alphabet in it, here's what would happen.

1. The DECLARE and SET create the variable and turns it from containing a NULL to having an empty string in it.

2. The SELECT reads the "first" row from the table and the content of row/column is added to the variable along with a "space" delimiter (the delimiter isn't important here) and reassigned to the variable kind of like say @counter = @counter + 1.

The variable now contains "A ".

3. The SELECT then reads the "second" row from the table and the same thing happens as in #2 above. The content of the second row is added to the variable and the result is stored back in the variable.

The variable now contatins "A B ".

4. The SELECT then reads the "third" row from the table and the same thing happens as in #2 and #3 above. The content of the second row is added to the variable and the result is stored back in the variable.

The variable now contatins "A B C ".

Unfortunately, the technique is a less-than-optimal method and can get quite slow as the variable gets bigger for reasons similar to why the old fashioned "bubble-sort" get's slower... It has to handle more and more data for each row added in. There's a trick with XML Path that you can do in a setbased fashion to concatenate related row information. Please see the following articles for more information...

http://www.sqlservercentral.com/articles/Test+Data/61572/
http://www.sqlservercentral.com/articles/comma+separated+list/71700/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1119969
Posted Tuesday, June 7, 2011 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:34 PM
Points: 15, Visits: 103
Jeff, thank you for taking the time to answer.

I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!
Post #1121169
Posted Wednesday, June 15, 2011 5:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 36,995, Visits: 31,519
jmaloney-1117255 (6/7/2011)
Jeff, thank you for taking the time to answer.

I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!


Apologies for the late response, again! I managed to burn the candle at both ends and at the middle.

Thank you very much for the kind words about the Tally Table article. I'm very happy to add you to the "Anti-RBAR Movement".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1126186
« Prev Topic | Next Topic »

Add to briefcase «««56789

Permissions Expand / Collapse