Advanced SQL Tutorial | Looping in SQL Server | WHILE Loops with Dynamic SQL

Advanced SQL Tutorial | Looping in SQL Server | WHILE Loops

In this training video, we walk through how to write a while loop in SQL in order to loop through a set of a columns that we want to breakout lead conversation rate by without copying and pasting code!

Here is the final query written for reference:

DECLARE @fields nvarchar(100)
DECLARE @field nvarchar(100)
DECLARE @comma int
DECLARE @query nvarchar(1000)

/*Update list with comma-separated list of variables to loop through*/
SET @fields = 'State,Marketing Channel,Lead Submission Month' + ','

WHILE @comma (ENTER GREATER THAN SYMBOL HERE SINCE YOUTUBE DOESN"T ALLOW IT IN DESCRIPTIONS) 0
BEGIN
PRINT 'Variable = ' + @field

SET @query = '
Select
[' + @field + '],
count(*) as [# Leads],
1.0000*sum(Converted)/count(*) as [% Converted]
from VALUE_DRIVEN_ANALYTICS.LOOP_ILLUSTRATION.LEADS
group by [' + @field + ']
order by 1.0000*sum(Converted)/count(*) desc
'
/*PRINT 'Query = ' + @query*/
EXEC sp_executesql @query

END

______________________________________________

CHAPTERS:
0:00 WHILE loop in sql server
0:18 Benefits of using loops
5:50 Avoiding rounding errors in SQL division
9:30 How to declare a variable in SQL
12:30 How to set the value of a SQL variable
14:00 How does charindex work in SQL
15:10 How does a WHILE loop work in SQL?
17:00 What does the LEFT function do in SQL?
18:09 How to print text in SQL
19:15 What does the STUFF function do in SQL?
23:45 How to write a dynamic SQL query
26:10 How to use the EXEC command in SQL
28:00 How to use sp_executesql in SQL

______________________________________________

ADDITIONAL VIDEOS YOU MAY LIKE:

Please subscribe to get updates on the latest analytics training videos available!

______________________________________________

ABOUT VALUE DRIVEN ANALYTICS:

Stay connected with us!

______________________________________________

KEYWORDS:

looping, sql looping, looping in sql, while loops, while loop, while loops in sql, sql while loops, sql while loop, while loop in sql, advanced sql, advanced sql tutorial, sql, data analyst, data scientist, data analytics, data science, analyst, analytics, data exploration, sql data exploration, automation, sql automation, charindex, dynamic SQL, left, stuff
#sql #dataanalyst #datanalytics #datascience #datascientist #sql

[InsAnalyst-vqsp]

I spent 4+ hours on this video itself and took detailed notes. Thank you for breaking down everything. I will study all the videos in your channel!

[jbingaman]

This was extremely helpful. I would love a video on how to export the results to a single excel sheet.

[renecarteir]

Really informative video. Thank you! Haven't looked at your other videos and maybe this one is a one off but the volume was a little low. Just an FYI.

[Sheepblagger]

That was great but I got a bit lost towards the end. I'm sure it will click when I try myself!

[ValueDrivenAnalytics]

What is your looping use case (don't include sensitive, confidential personal or organization data)?

[InsAnalyst-vqsp]

Can you please recommend some free sources where I can learn advanced SQL coding (like this) and database management please?