Syntax of for-loop in SQL Server
T-SQL doesn't have a FOR
loop, it has a WHILE
loop
WHILE (Transact-SQL)
WHILE Boolean_expression
BEGIN
END
How to loop a query in different databases in T-SQL?
I like creating a temp table for the query results, creating a table variable for my list of dbs to query, and then looping through that list of databases to execute the query for each, inserting the results into the temp table.
This way you can query the results repeatedly after running the whole loop process only once.
Try this (you'll need to modify the #results temp table declaration and the where clause of the first insert statement):
--drop temp table if it exists; this clears the results each time you run the full script
if object_id('tempdb..#results') is not null
drop table #results
go
--recreate temp table; this is used for storing the result set. It's created as a temp table instead of a table variable so that it can be queried repeatedly after gathering results.
create table #results (
dbName varchar(128)
,cust_num varchar(128)
, [name] varchar(128)
, credit_hold bit
, credit_hold_reason varchar(128)
, [Type of credit hold reason] varchar(128)
, credit_hold_date varchar(128)
, credit_limit int); --adjust column declaration as needed for query results
--create a variable to track the database list
declare @dbList table (dbName varchar(128), indx int)
insert into @dbList
select dbName = name, row_number() over (order by name)
from master.sys.databases
--where --insert your own where clause here to narrow down which databases to run the query on.
--declare variables for use in the while loop
declare @index int = 1
declare @totalDBs int = (select count(*) from @dbList)
declare @currentDB varchar(128)
declare @cmd varchar(300)
--define the command which will be used on each database.
declare @cmdTemplate varchar(300) = '
use {dbName};
insert into #results
select db_name(), cust_num, name, credit_hold, credit_hold_reason,
(case
when credit_hold_reason = ''NP'' then ''No payments''
when credit_hold_reason = ''UK'' then ''Unknown''
when credit_hold_reason = ''BK'' then ''Bankruptcy''
end) as ''Type of credit hold reason'',
credit_hold_date, credit_limit
from [custaddr]
order by credit_hold_reason asc
'
--loop through each database and execute the command
while @index <= @totalDBs
begin
set @currentDB = (select dbName from @dbList where indx = @index)
set @cmd = replace(@cmdTemplate, '{dbName}', @currentDB)
execute(@cmd)
set @index += 1
end
--see the results of the command; this can be queried repeatedly by itself after it's created the first time
select *
from #results
Note that looping through a table variable is more efficient than declaring a cursor.
Looping through all tables and then looping through all column datatypes
You can build the statements in a string and execute it dynamically. That way you don't have a need for while-loops.
DECLARE @alter_stmts NVARCHAR(MAX) = (
SELECT
';ALTER TABLE '+
QUOTENAME(t.TABLE_SCHEMA)+'.'+QUOTENAME(t.TABLE_NAME)+' '+
'ALTER COLUMN '+
QUOTENAME(c.COLUMN_NAME)+ ' NVARCHAR(MAX) ' + CASE WHEN c.IS_NULLABLE='YES' THEN 'NULL' ELSE 'NOT NULL' END
FROM
INFORMATION_SCHEMA.TABLES AS t
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON
c.TABLE_SCHEMA=t.TABLE_SCHEMA AND
c.TABLE_NAME=t.TABLE_NAME
WHERE
t.TABLE_TYPE='BASE TABLE' AND
c.DATA_TYPE LIKE '%NTEXT%'
FOR
XML PATH('')
)+';';
--SELECT @alter_stmts; -- review
EXEC sp_executesql @alter_stmts; -- execute it
Why is the same value being return in this SQL While Loop?
The issue happens because SQL Server doesn't associate @row
with a row in the table (the correlation is obvious to you, but SQL Server isn't human).
As you loop through the numbers 1 -> @count
, it is running the same SELECT @ABC = ABC FROM #TMP_ABC
over and over again. There is no WHERE
clause and no TOP
so SQL Server is just reading the whole table every time, and setting the variable equal to the last ABC
value it read.
Instead, you should use a cursor (if you need to loop at all; usually you don't, per @Larnu's comment). You have have read some misinformation somewhere that cursors are bad and that while loops are not cursors, but these are both false.
- Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR
- What impact can different cursor options have?
- Follow-up on cursor options
- Overlooked T-SQL Gems (see why using a local variable for a cursor is even better than the regular type you probably use)
If you do in fact need to loop for some reason, here's a rewrite:
CREATE TABLE #TMP_ABC(ABC varchar(3));
INSERT INTO #TMP_ABC(ABC) VALUES
('AAA'),('BBB'),('CCC'),('DDD'),('EEE'),('FFF');
DECLARE @ABC varchar(3), @c cursor;
SET @c = cursor LOCAL FAST_FORWARD
FOR SELECT ABC FROM #TMP_ABC;
OPEN @c;
FETCH NEXT FROM @c INTO @ABC;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ABC;
FETCH NEXT FROM @c INTO @ABC;
END
Output:
AAA
BBB
CCC
DDD
EEE
FFF
- Example db<>fiddle
But I'm not sure what that accomplishes over SELECT ABC FROM #TMP_ABC;
.
Do while loop in SQL Server 2008
I am not sure about DO-WHILE IN MS SQL Server 2008 but you can change your WHILE loop logic, so as to USE like DO-WHILE loop.
Examples are taken from here: http://blog.sqlauthority.com/2007/10/24/sql-server-simple-example-of-while-loop-with-continue-and-break-keywords/
Example of WHILE Loop
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO
ResultSet:
1
2
3
4
5Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
BREAK;
END
GO
ResultSet:
1
2
3Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
CONTINUE;
IF @intFlag = 4 -- This will never executed
BREAK;
END
GO
ResultSet:
1
2
3
4
5
But try to avoid loops at database level.
Reference.
SQL fill next date (month) with loop
Paul, I'm assuming that you forgot to specify the month in your mock data.
I hope the code below may help you understand how non-trivial is what you are trying to accomplish :-) Kudos for your will to get rid of loops.
To make it better, I propose a denormalization (CAUTION!):
- create another column
price_valid_until
- the latest prices records will have
price_valid_until = '21000101'
(aka, far away in the future) - when registering a new price, update the previous with
new price_valid_from - 1 day
Here's the solution, with a pretty complex, but efficient query (http://sqlfiddle.com/#!18/4ab23/4)
create table price_history(
SKU varchar(255),
price_valid_from date,
price decimal(16, 2)
)
insert into price_history
values
('a', '20210101', 10),
('a', '20210107', 12),
('b', '20210102', 4),
('b', '20210110', 2),
('b', '20210214', 5);
-- This fiddler won't let me initialize and reference:
--
-- declare
-- @from_date date,
-- @to_date date;
--
-- select
-- @from_date = min(date_from),
-- @to_date = max(date_from)
-- from price_history
with
date_range as(
select
min(price_valid_from) as from_date,
--
eomonth(
max(price_valid_from)
) as to_date
from price_history
),
--
all_dates as(
select from_date as date_in_range
from date_range
-- ----------
union all
-- ----------
select dateadd(day, 1, date_in_range)
from all_dates
where
date_in_range < (
select to_date
from date_range
)
),
--
price_history_boundaries as(
select
ph.SKU,
ph.price,
--
ph.price_valid_from,
-- The latest price, so far, is valid until 01/01/2100
coalesce(
dateadd(
day,
-1,
min(ph_next.price_valid_from)
),
'21000101'
) as price_valid_until
from
price_history ph
left outer join price_history ph_next
on(
ph_next.SKU = ph.SKU
and ph_next.price_valid_from > ph.price_valid_from
)
group by ph.SKU, ph.price_valid_from, ph.price
)
select
phb.SKU,
ad.date_in_range,
phb.price
from
all_dates ad
inner join price_history_boundaries phb
on(
phb.price_valid_from <= ad.date_in_range
and phb.price_valid_until >= ad.date_in_range
)
order by phb.SKU, ad.date_in_range
Related Topics
How to Limit the Results on a SQL Query
Join Versus Exists Performance
Xquery - How to Use the SQL:Variable in 'Value()' Function
Do Clustered Index on a Column Guarantees Returning Sorted Rows According to That Column
Case Statement with Different Data Type
SQL Select for All Records That May Holds Specific Value
How to Generate All N-Grams in Hive
Conversion Failed When Converting from a Character String to Uniqueidentifier Error in SQL Server
Comma Delimited SQL String Need to Separated
What Is the Affect of Convert() on Index While Searching
Generating Xml File from SQL Server 2008
Postgres 9.4 JSONb Array as Table
Rollback Multiple SQL Update Queries in Ms Access
How to Select Records That Don't Exist in SQL Server
Months Between Two Dates in SQL Server with Starting and End Date of Each of Them in SQL Server
Ms Access Displaying Vba Select Query in Datasheet
How to Use a Function-Based Index on a Column That Contains Nulls in Oracle 10+