April 25, 2012 at 2:19 am
I am a big fan of Dwain's Signature - No Cursors! No Loops........
I definitely know that Cursors are poor performers and should be avoided.
But, what if there is a Business requirement when you have to loop through a table and Select Names (from the "Name" column of the table) from the table, one by one and one after the other.
In this case I know that I can use a Cursor or may be a loop or a temp table to avoid the Cursor.
But I want to know if there is any other alternative in this scenario?
Can this be done using Tally Table?
PS: not posting DDL or sample data coz its a general question.
April 25, 2012 at 3:07 am
You can make use of WHILE[/url] loop or Recursive Queries Using Common Table Expressions
======================================
Blog: www.irohitable.com
April 25, 2012 at 3:17 am
I am not sure about either of these because both of them have performance limitations.
Can Tally Tables be used in this scenario??
I'm looking to throw away Cursors and Loops from my life....:-D 😀
This is what's standing in my way 😉
April 25, 2012 at 3:54 am
vinu512 (4/25/2012)
I am not sure about either of these because both of them have performance limitations.Can Tally Tables be used in this scenario??
I'm looking to throw away Cursors and Loops from my life....:-D 😀
This is what's standing in my way 😉
Whether or not you can use tally table or other set based solutions depend on what do you want to do with the data. In general, most of the times things can be done using set based code instead of using cursors.
April 25, 2012 at 5:02 am
vinu512 (4/25/2012)
I am a big fan of Dwain's Signature - No Cursors! No Loops........I definitely know that Cursors are poor performers and should be avoided.
But, what if there is a Business requirement when you have to loop through a table and Select Names (from the "Name" column of the table) from the table, one by one and one after the other.
In this case I know that I can use a Cursor or may be a loop or a temp table to avoid the Cursor.
But I want to know if there is any other alternative in this scenario?
Can this be done using Tally Table?
PS: not posting DDL or sample data coz its a general question.
The business scenario you have given is not specific enough( at least not to me ).
As you said, your requirement is general and you are getting general answers.
There is no general replacement to CURSORS. It all depends on the specific requirement.
Please post the scenario in more detail and DDL along with some sample data would definitely help.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2012 at 5:32 am
Suppose, I have this DDL and Sample Data:
--DDL
Create Table Ex(Name varchar(30) )
--Sample Data
Insert Into Ex
Select 'Jack'
Union all
Select 'Vinu'
Union all
Select 'Jim'
Union all
Select 'Stan'
Union all
Select 'Ash'
I want to loop through the table, Select the names one by one into temporary variables and print the variables.
Can this be done using a tally table?
April 25, 2012 at 5:34 am
Why are you doing this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2012 at 5:42 am
vinu512 (4/25/2012)
Suppose, I have this DDL and Sample Data:
--DDL
Create Table Ex(Name varchar(30) )
--Sample Data
Insert Into Ex
Select 'Jack'
Union all
Select 'Vinu'
Union all
Select 'Jim'
Union all
Select 'Stan'
Union all
Select 'Ash'
I want to loop through the table, Select the names one by one into temporary variables and print the variables.
Can this be done using a tally table?
This seems to be an over simplified example of your actual problem. Inserting names simply to print them doesn't make a common requirement.
Also, I don't understand why you would insert the rows one by one into the temporary table, when you can do it at one go
If you are doing some other complex operations for every person whose name you have given, may be its not possible to remove the CURSOR.
As an example, imagine you have to send a separate birthday mail with different mail body to each of these people using DBMail , you can't avoid a CURSOR
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2012 at 5:53 am
If you are doing some other complex operations for every person whose name you have given, may be its not possible to remove the CURSOR.
As an example, imagine you have to send a separate birthday mail with different mail body to each of these people using DBMail , you can't avoid a CURSOR
Thanx Kingston Dhasian....that's exactly what I wanted to ask.....so for such tasks I can't avoid the Cursor....right??.....(Just confirming)
@Phil Parkin : This is not an actual scenario....I just wanted to quote a simple example to make things easier. The actual scenario could be something like what I quoted from Kingston Dhasian's reply. I just wanted to know if Cursors are always avoidable or in certain scenarios(like the one mentioned by Kingston Dhasian) Cursors are unavoidable.
PS : I am pretty new here and it hasn't been much time since I started working with SQL Server. So, I'm still and (I guess) always would be on a Learning Curve. I know this site is not a tution class or anything like that....but then where would I get so many SQL experts all at the same place??....:-D
April 25, 2012 at 6:03 am
vinu512 (4/25/2012)
Suppose, I have this DDL and Sample Data:
--DDL
Create Table Ex(Name varchar(30) )
--Sample Data
Insert Into Ex
Select 'Jack'
Union all
Select 'Vinu'
Union all
Select 'Jim'
Union all
Select 'Stan'
Union all
Select 'Ash'
I want to loop through the table, Select the names one by one into temporary variables and print the variables.
Can this be done using a tally table?
SET NOCOUNT ON;
--DDL
CREATE TABLE Ex (NAME VARCHAR(30));
--Sample Data
INSERT INTO Ex
SELECT 'Jack'
UNION ALL SELECT 'Vinu'
UNION ALL SELECT 'Jim'
UNION ALL SELECT 'Stan'
UNION ALL SELECT 'Ash';
--No Cursors
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = COALESCE(@SQL,'') + CHAR(13) + CHAR(10) +
'PRINT ' + CHAR(39) + NAME + CHAR(39) + ';'
FROM Ex;
SET @sql = STUFF(@SQL,1,2,'');
EXECUTE sp_executesql @sql;
Results in: -
Jack
Vinu
Jim
Stan
Ash
April 25, 2012 at 6:17 am
Cadavre, i can accomplish that result by this query:
Select * From Ex
😀
But I want the results as follows:
Declare
@temp1 varchar(30) = 'Jack',
@temp2 varchar(30) = 'Vinu',
@temp3 varchar(30) = 'Jim',
@temp4 varchar(30) = 'Stan',
@temp5 varchar(30) = 'Ash'
Select @temp1, @temp2, @temp3, @temp4, @temp5
Instead of the part where I am hard coding values for the temp variables, I want to select the names....one by one into these variables and then print them one by one...or may be use these variables elsewhere.
Its more of like doing a Split....where instead of the string you have columns of a table.
Does that make things a little clearer??
April 25, 2012 at 6:50 am
vinu512 (4/25/2012)
Cadavre, i can accomplish that result by this query:
Select * From Ex
😀
But I want the results as follows:
Declare
@temp1 varchar(30) = 'Jack',
@temp2 varchar(30) = 'Vinu',
@temp3 varchar(30) = 'Jim',
@temp4 varchar(30) = 'Stan',
@temp5 varchar(30) = 'Ash'
Select @temp1, @temp2, @temp3, @temp4, @temp5
Instead of the part where I am hard coding values for the temp variables, I want to select the names....one by one into these variables and then print them one by one...or may be use these variables elsewhere.
Its more of like doing a Split....where instead of the string you have columns of a table.
Does that make things a little clearer??
Method 1:
SET NOCOUNT ON;
--DDL
CREATE TABLE Ex (NAME VARCHAR(30));
--Sample Data
INSERT INTO Ex
SELECT 'Jack'
UNION ALL SELECT 'Vinu'
UNION ALL SELECT 'Jim'
UNION ALL SELECT 'Stan'
UNION ALL SELECT 'Ash';
--No Cursors
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = COALESCE(@SQL,'') + ',' + CHAR(39) + NAME + CHAR(39) + ''
FROM Ex;
SET @sql = 'SELECT ' + STUFF(@SQL,1,1,'');
EXECUTE sp_executesql @sql;
Results in: -
---- ---- ---- ---- ----
Jack Vinu Jim Stan Ash
Method 2:
SET NOCOUNT ON;
--DDL
CREATE TABLE Ex (NAME VARCHAR(30));
--Sample Data
INSERT INTO Ex
SELECT 'Jack'
UNION ALL SELECT 'Vinu'
UNION ALL SELECT 'Jim'
UNION ALL SELECT 'Stan'
UNION ALL SELECT 'Ash';
--No Cursors
SELECT
MAX(CASE WHEN rn = 1 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 2 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 3 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 4 THEN NAME ELSE NULL END),
MAX(CASE WHEN rn = 5 THEN NAME ELSE NULL END)
FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM Ex) a;
Results in: -
---- ---- ---- ---- ----
Jack Vinu Jim Stan Ash
Method 3
SET NOCOUNT ON;
--DDL
CREATE TABLE Ex (NAME VARCHAR(30));
--Sample Data
INSERT INTO Ex
SELECT 'Jack'
UNION ALL SELECT 'Vinu'
UNION ALL SELECT 'Jim'
UNION ALL SELECT 'Stan'
UNION ALL SELECT 'Ash';
--No Cursors
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = COALESCE(@SQL,'') +
',' + CHAR(13) + CHAR(10) +
'MAX(CASE WHEN rn = ' + CAST(rn AS NVARCHAR(5)) + ' THEN NAME ELSE NULL END)'
FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM Ex) a;
SET @sql = 'SELECT' + STUFF(@SQL,1,1,'') + CHAR(13) + CHAR(10) + 'FROM (SELECT NAME, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn' +
CHAR(13) + CHAR(10) + 'FROM Ex) a;';
EXECUTE sp_executesql @sql;
Results in: -
---- ---- ---- ---- ----
Jack Vinu Jim Stan Ash
Btw: -
vinu512 (4/25/2012)
Cadavre, i can accomplish that result by this query:
Select * From Ex
😀
Not true. My query was executing a PRINT statement per item from the table, whereas a SELECT * is not.
April 25, 2012 at 7:00 am
OK...my bad...lets forget about printing the names.
I just want the names to be stored in 5 temp variables so that I can use them further.
Can that be done without cursor or loop?
April 25, 2012 at 7:04 am
There are uses for cursors. Determining when has to happen on a case by case basis. There is no general rule as to when you use them and when you don't. Many times you can find set based alternatives. Sometimes, it just takes longer to find them.
This exercise in general terms just isn't worth playing.
April 25, 2012 at 11:55 pm
Vinus - Glad you like my signature!
No you don't need a loop or even dynamic SQL to do this one.
DECLARE @Table TABLE(Name varchar(30) )
DECLARE @var VARCHAR(MAX)
--Sample Data
Insert Into @Table
Select 'Jack' Union all Select 'Vinu' Union all Select 'Jim'
Union all Select 'Stan' Union all Select 'Ash'
SELECT @var = STUFF((SELECT CHAR(10)+[Name]FROM @TableFOR XML PATH('')), 1, 1, '')
PRINT @var
Any opportunity I can get to "correct" an example using a CURSOR to one without, I'll jump on immediately if I see it. If I don't, feel free to PM me.
Edit: Sorry. Didn't see the 2nd page of this thread so I know now you don't just want to print.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 59 total)
You must be logged in to reply to this topic. Login to reply