November 22, 2011 at 5:15 am
I'm wondering if this is possible, i want to execute some code for each user and for every education for that user.
I have this structure:
USER(id, firstname)
EDUCATION(id,userid,school)
This code is to loop through every user:
declare @u_id char( 11 )
set rowcount 0
select TOP 10 * into #mytemp from user
set rowcount 1
select @u_id = id from #mytemp
while @@rowcount <> 0
begin
set rowcount 0
select firstname from #mytemp where id = @u_id
delete #mytemp where id = @u_id
set rowcount 1
select @u_id = id from #mytemp
end
set rowcount 0
What i want to achieve is this output:
name:'Robert'
school:'IT'
school:'Management'
firstname:'George'
school:'Webb'
In other words, for each row in USER get every row in EDUCATION.
Something like this code:
declare @u_id char( 11 )
declare @e_id char( 11 )
set rowcount 0
select TOP 10 * into #mytemp from users WHERE id=8005
set rowcount 1
select @u_id = id from #mytemp
while @@rowcount <> 0
begin
set rowcount 0
select firstname from #mytemp where id = @u_id
delete #mytemp where id = @u_id
set rowcount 1
SELECT * INTO #mytemp2 FROM usereducation WHERE usereducation.userid=@u_id
SELECT @e_id = id FROM #mytemp2
WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0
SELECT school from #mytemp2 WHERE id=@e_id
DELETE #mytemp2 WHERE id = @e_id
SET ROWCOUNT 1
SELECT @e_id = id FROM #mytemp2
END
DROP TABLE #mytemp2
select @u_id = id from #mytemp
end
set rowcount 0
drop table #mytemp
The code above gets just the first school for current, and then jumps to next user.
Thanks for your time.
November 22, 2011 at 5:59 am
Oh great, i got it! Did'nt really know what rowcount did, changed it around and it works 😀
declare @u_id char( 11 )
declare @e_id char( 11 )
set rowcount 0
select TOP 10 * into #mytemp from users WHERE id=8005
set rowcount 1
select @u_id = id from #mytemp
while @@rowcount <> 0
begin
set rowcount 0
select firstname from #mytemp where id = @u_id
delete #mytemp where id = @u_id
SELECT * INTO #mytemp2 FROM usereducation WHERE usereducation.userid=@u_id
set rowcount 1
SELECT @e_id = id FROM #mytemp2
WHILE @@ROWCOUNT <> 0
BEGIN
SET ROWCOUNT 0
SELECT school from #mytemp2 WHERE id=@e_id
DELETE #mytemp2 WHERE id = @e_id
SET ROWCOUNT 1
SELECT @e_id = id FROM #mytemp2
END
DROP TABLE #mytemp2
select @u_id = id from #mytemp
end
set rowcount 0
drop table #mytemp
November 22, 2011 at 8:06 am
You know looping is absolutely horrible for performance (and you have nested loops which is even worse). SQL is designed to work set based and not row by row. Given that all you are doing is selecting data there is absolutely no need to do this using loops. If you want to try a set based approach to this post up some ddl and sample data and we can have a go at it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 22, 2011 at 8:12 am
best guess:
SELECT .id,
.firstname,
[EDUCATION].school
FROM
LEFT OUTER JOIN [EDUCATION]
ON [EDUCATION].userid = .id
Lowell
November 23, 2011 at 12:48 am
Sean Lange (11/22/2011)
You know looping is absolutely horrible for performance (and you have nested loops which is even worse).
Yes, i am aware of that. I am trying to build a mailing service for our customers, using database mail. A weekly report email that needs nested loops to get the data needed.
I'm very new to this, and dont know what approach to take, either VBScript or DBmail. Both going to be data intensive and most probably slow. What i like about DBMail are Jobs and Schedules.
November 23, 2011 at 5:53 am
memymasta (11/23/2011)
Yes, i am aware of that. I am trying to build a mailing service for our customers, using database mail. A weekly report email that needs nested loops to get the data needed.I'm very new to this, and dont know what approach to take, either VBScript or DBmail. Both going to be data intensive and most probably slow. What i like about DBMail are Jobs and Schedules.
Your example involving user and Education tables is really at odds with an email campaign.
here's a rough example i just sketched out that woudl use a cursor to send an individual a customized email.
DECLARE
@isql VARCHAR(2000),
@name varchar(30),
@email varchar(100),
@LastWishlistItem varchar(500),
@MailSubject varchar(200),
@MailBody varchar(max)
DECLARE c1 CURSOR FOR
SELECT name,
email,
LastWishlistItem
FROM YourTable
--limit to one email per week, no matter which campaign they might have been a part of
WHERE LastContactDate > dateadd(dd,-7,getdate())
OPEN c1
FETCH next FROM c1 INTO @name,@email,@LastWishlistItem
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @MailSubject = @name + ', Never Before Offered Email Exclusive Now Through Black Friday'
SELECT @MailBody = @LastWishlistItem + ' Now 25% Off in this exclusive offer.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='My Default DBMail',
@recipients=@email,
@subject = @MailSubject,
@body = @MailBody,
@body_format = 'HTML'
--@body_format = 'TEXT'
FETCH next FROM c1 INTO @name,@email,@LastWishlistItem
END
CLOSE c1
DEALLOCATE c1
GO
Lowell
November 23, 2011 at 6:48 am
Lowell (11/23/2011)
Your example involving user and Education tables is really at odds with an email campaign.
here's a rough example i just sketched out that woudl use a cursor to send an individual a customized email.
Interesting example, although you're fetching data from single table...
A more suiting scenario would be an auction site service. Where we want to build survailance on specific items that users can specify. Each week the users will get an email with new items that suit their criteria.
We have three tables, , [ITEM] and [USERITEM]. Where they have following columns:
(id, firstname, email)
[ITEM](id, item_name)
[USERITEM](id, user_id, item_id, surv_date)
If a user is survailing items like: chair, table, lamp. The perfect mail would look like this:
Hi David
This week we have this new items:
table1
chair1
lamp1
lamp2
Bye
The logics is we need to loop through every user to send email to each one. Also we need to loop through items so we can add them to HTML string inside the @MailBody.
November 23, 2011 at 7:01 am
memymasta (11/23/2011)
Lowell (11/23/2011)
Your example involving user and Education tables is really at odds with an email campaign.
here's a rough example i just sketched out that woudl use a cursor to send an individual a customized email.
Interesting example, although you're fetching data from single table...
A more suiting scenario would be an auction site service. Where we want to build survailance on specific items that users can specify. Each week the users will get an email with new items that suit their criteria.
We have three tables, , [ITEM] and [USERITEM]. Where they have following columns:
(id, firstname, email)
[ITEM](id, item_name)
[USERITEM](id, user_id, item_id, surv_date)
If a user is survailing items like: chair, table, lamp. The perfect mail would look like this:
Hi David
This week we have this new items:
table1
chair1
lamp1
lamp2
Bye
The logics is we need to loop through every user to send email to each one. Also we need to loop through items so we can add them to HTML string inside the @MailBody.
a single select could join all your tables together, and have all the required data in a single row...you want to get away from thinking "i've got to loop" to get the data.
for example, you can use FOR XML to gather multiple rows of data into a comma delimited list (if that was the right thing for the email)
SELECT DISTINCT top 10
t.name,
sq.Columns
FROM sys.tables t
JOIN (
SELECT OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,'')
FROM sys.columns s
) sq ON t.object_id = sq.object_id
Lowell
November 23, 2011 at 7:32 am
memymasta (11/23/2011)
The logics is we need to loop through every user to send email to each one. Also we need to loop through items so we can add them to HTML string inside the @MailBody.
You don't need loops for this. We have a system that sends out similar emails and there are no loops used at all. We use FOR XML PATH() to create the body of the email and we also use it to create dynamic SQL to send separate emails to each user.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 23, 2011 at 7:52 am
That's really exciting, wasn't aware of the FOR XML option! SQLServer surprises me on daily basis with it's functionality.
Ty for the example it will help me out alot.
@drew.allen
Wait, so, no loops at all? Even for @recipient mail value? That sound almost to good to be true... could you please tell me a little bit more?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply