DECLARE @CNT INT
More than one variable can be defined with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you separate each variable definition with a comma, like so:
DECLARE @CNT INT, @X INT, @Y INT, @Z CHAR(10)
Above I have defined 4 local variables with a single DECLARE statement. A local variable is initially assigned a NULL value. A value can be assigned to a local variable by using the SET or SELECT statement. On the SET command you specify the local variable and the value you wish to assign to the local variable. Here is an example of where I have defined my @CNT variable and then initialize the variable to 1.
DECLARE @CNT INT SET @CNT = 1
Here is an example of how to use the SELECT statement to set the value of a local variable.
DECLARE @ROWCNT int SELECT @ROWCNT=COUNT(*) FROM pubs.dbo.authors
The above example sets the variable @ROWCNT to the number of rows in the pubs.dbo.authors table.
Declare @Country varchar(25) set @Country = 'Germany' select CompanyName from Northwind.dbo.Customers where Country = @Country
Declare @x int set @x = 29 if @x = 29 print 'The number is 29' if @x = 30 print 'The number is 30'
The above code prints out only the phrase "The number is 29", because the first IF statement evaluates to true. Since the second IF is false the second print statement is not executed.
if (select count(*) from Pubs.dbo.Authors
where au_lname like '[A-D]%') > 0
print 'Found A-D Authors'
Here I printed the message "Found A-D Authors" if the SELECT statement found any authors in the pubs.dbo.authors table that had a last name that started with an A, B, C, or D.
if db_name() = 'master'
begin
Print 'We are in the Master Database'
Print ''
Print 'So be careful what you execute'
End
Above a series of "PRINT" statements will be executed if this IF statement is run in the context of the master database. If the context is some other database then the print statements are not executed.
if exists(select * from Northwind.dbo.Customers
where CustomerId = 'ALFKI')
Print 'Need to update Customer Record ALFKI'
else
Print 'Need to add Customer Record ALFKI'
if exists(select * from Northwind.dbo.Customers
where CustomerId = 'LARSE')
Print 'Need to update Customer Record LARSE'
else
Print 'Need to add Customer Record LARSE'
The first IF...ELSE logic checks to see it CustomerId 'ALFKI' exists. If it exists it prints the message "Need to update Customer Record", if it doesn't exist the "Need to add Customer Record" is displayed. This logic is repeated for CustomerId = 'LARS'. When I run this code against my Northwind database I get the following output.
Need to update Customer Record ALFKI Need to add Customer Record LARSE
As you can see from the results CustomerId 'ALFKI' existed, because the first print statement following the first IF statement was executed. Where as in the second IF statement CustomerId 'LARSE' was not found because the ELSE portion of the IF...ELSE statement was executed.
-- Single IF Statement with multiple conditions
use Northwind
if db_name() = 'Northwind' and
(select count(*) from sysobjects
where name = 'Customers') = 1
print 'Table Customers Exist'
else
print 'Not in the Northwind database' +
' or Table Customer does not exist'
-- Nested IF Statements
use Northwind
if db_name() = 'Northwind'
if (select count(*) from sysobjects
where name = 'Customers') = 1
print 'Table Customers Exist'
else
print 'Table Customer does not exist'
else
print 'Not in the Northwind Database'
As you can see I tested to see if the query was being run from the Northwind database and whether the "Customers" table can be found in sysobjects. If this was true, I printed the message "Table Customers Exists". In the first example I had multiple conditions in a single IF statement. Since I was not able to determine which parts of the conditions in the IF statement where false the ELSE portion printed the message "Not in Northwind database or Table Customer does not exist". Now in the second example where I had a nested IF statement I was able to determine whether I was in the wrong database or the object "Customers" did not exist. This allowed me to have two separate print statements to reflect exactly what condition was getting a false value.
DECLARE cursor_name CURSOR FOR select_statement
declare @CustId nchar(5)
declare @RowNum int
declare CustList cursor for
select top 5 CustomerID from Northwind.dbo.Customers
OPEN CustList
FETCH NEXT FROM CustList
INTO @CustId
set @RowNum = 0
WHILE @@FETCH_STATUS = 0
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @CustId
FETCH NEXT FROM CustList
INTO @CustId
END
CLOSE CustList
DEALLOCATE CustList
1 ALFKI 2 ANATR 3 ANTON 4 AROUT 5 BERGS
declare @CustId nchar(5)
declare @RowNum int
select top 1 @CustId=CustomerID from Northwind.dbo.Customers
set @RowNum = 0
WHILE @RowNum < 5
BEGIN
set @RowNum = @RowNum + 1
print cast(@RowNum as char(1)) + ' ' + @CustId
select top 1 @CustId=CustomerID from Northwind.dbo.Customers
where CustomerId > @CustID
END
Here you can see the first SELECT statement selects only the first CustomerID. This ID is placed in the local variable @CustID. The WHILE loop is controled by the local variable @RowNum. Each time through the WHILE loop, the Row Number and CustomerID are printed out. Prior to returning to the top of the WHILE loop I used another "SELECT TOP 1" statement to select the next CustomerID. This SELECT statement uses a WHERE clause on the SELECT statement to select the first CustomerID that is greater than the CustomerID that was just printed. The WHILE loop is process 5 times, allowing the SELECT TOP 1 method to retrieve the top 5 CustomerID's one records at a time. This example produces the same printed output as my prior CURSOR example.
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
Where [@stmt =] stmt is the dynamic T-SQL statement that you would like to execute, [@params =] N'@parameter_name data_type [,...n]' is used to identify the list of variables and the data type for each dynamic variable that is contained in the dynamic T-SQL statement, and [@param1 =] 'value1' [,...n] is used to provide values for each variable used in the dynamic T-SQL statement being executed. More information about this stored procedure, and how to use it can be found in Microsoft SQL Server Books Online.
use Northwind
go
declare @RECCNT int
declare @ORDID varchar(10)
declare @CMD Nvarchar(100)
set @ORDID = 10436
SET @CMD = 'SELECT @RECORDCNT=count(*) from [Orders]' +
' where OrderId < @ORDERID'
print @CMD
exec sp_executesql @CMD,
N'@RECORDCNT int out, @ORDERID int',
@RECCNT out,
@ORDID
print 'The number of records that have an OrderId' +
' greater than ' + @ORDID + ' is ' +
cast(@RECCNT as char(5))
In this example you can see that the table from which I am returning the record count is the "Orders" table in the "Northwind" database. To make this code dynamic, the WHERE statement criteria is built on the fly by using a value passed in to the sp_executesql SP call which will set the @ORDERID variable in the dynamic T-SQL string. The record count for the number of Orders records that have an OrderId less than the @ORDERID variable value will be returned in a variable named @RECORDCNT. Both these local variables are specified in the dynamic T-SQL command, which is passed to sp_executesql SP in the string @CMD. These variables are not declared the calling T-SQL code, but instead are declared within the sp_executesql SP.
SELECT @RECORDCNT=count(*) from [Orders] where OrderId < @ORDERID
Since this T-SQL statement contains variables, the sp_executesql (SP) requires that the calling T-SQL code identify these variables, and their format in the parameters passed to this SP. Specifying the definition of these variables is done in the second parameter of the sp_executesql call above (N'@RECORDCNT int out, @ORDERID int'). As you can see the @RECORDCNT variable is defined as output. This allows this variable to return a value from the sp_executesql to the calling T-SQL code. Defining the definitions of these variables allows the sp_executesql SP to define these variables locally within the SP code.
The number of records that have an OrderId greater than 10436 is 188
select top 1 OrderId,convert(char(10),
OrderDate,121) Last_Paris_Order,
(select convert(char(10),max(OrderDate),121) from
Northwind.dbo.Orders) Last_OrderDate,
datediff(dd,OrderDate,
(select Max(OrderDate)from Northwind.dbo.Orders)) Day_Diff
from Northwind.dbo.Orders
where ShipCity = 'Paris'
order by OrderDate desc
The above code contains two subqueries. The first subquery gets the OrderDate for the last order shipped regardless of ShipCity, and the second subquery calculates the number of days between the two different OrderDates. Here I used the first subquery to return a column value in the final result set. The second subquery was used as a parameter in a function call. This subquery passed the "max(OrderDate)" date to the DATEDIFF function.
select distinct country from Northwind.dbo.Customers where country not in (select distinct country from Northwind.dbo.Suppliers)
Here I have returned a list of countries where customers live, but there is no supplier located in that country. I suppose if you where trying to provide better delivery time to customers, then you might target these countries to look for additional suppliers.
select Country,CompanyName, ContactName, ContactTitle, Phone
from Northwind.dbo.Customers
where country =
(select top 1 country
from Northwind.dbo.Customers C
join
Northwind.dbo.Orders O
on C.CustomerId = O.CustomerID
group by country
order by count(*))
Here I have written a subquery that joins the Customer and Orders Tables to determine the total number of orders for each country. The subquery uses the "TOP 1" clause to return the country with the fewest number of orders. The country with the fewest number of orders is then used in the WHERE clause to determine which Customer Information will be displayed.
select au_lname, au_fname, title from
(select au_lname, au_fname, au_id from pubs.dbo.authors
where state = 'CA') as a
join
pubs.dbo.titleauthor ta on a.au_id=ta.au_id
join
pubs.dbo.titles t on ta.title_id = t.title_id
Here I have used a subquery to select only the author record information, if the author's record has a state column equal to "CA." I have named the set returned from this subquery with a table alias of "a." I can then use this alias elsewhere in the T-SQL statement to refer to the columns from the subquery by prefixing them with an "a", as I did in the "ON" clause of the "JOIN" criteria. Sometimes using a subquery in the FROM clause reduces the size of the set that needs to be joined. Reducing the number of records that have to be joined enhances the performance of joining rows, and therefore speeds up the overall execution of a query.
set nocount on
create table x(i int identity,
a char(1))
insert into x values ('a')
insert into x values ('b')
insert into x values ('c')
insert into x values ('d')
select * from x
update x
set a = b.a
from (select max(a) as a from x) b
where i > 2
select * from x
drop table x
Here I created a table named "x," that has four rows. Then I proceeded to update the rows where "i" was greater than 2 with the max value in column "a". I used a subquery in the FROM clause of the UPDATE statement to identity the max value of column "a."
select pub_name, count(*) bookcnt
from pubs.dbo.titles t
join
pubs.dbo.publishers p
on t.pub_id = p.pub_id
group by pub_name
having p.pub_name in
(select pub_name from pubs.dbo.publishers where state <> 'CA')
Here my subquery returns the pub_name values for all publishers that have a state value not equal to "CA." The HAVING condition then checks to see if the pub_name is in the set returned by my subquery.
select distinct OrderId
from Northwind.dbo.[Order Details] OD
where
Quantity <l (select avg(Quantity) * .1
from Northwind.dbo.[Order Details]
where OD.ProductID = ProductID)
The correlated subquery in the above command is contained within the parenthesis following the greater than sign in the WHERE clause above. Here you can see this correlated subquery contains a reference to "OD.ProductID". This reference compares the outer query's "ProductID" with the inner query's "ProductID". When this query is executed, the SQL engine will execute the inner query, the correlated subquery, for each "[Order Details]" record. This inner query will calculate the average "Quantity" for the particular "ProductID" for the candidate row being processed in the outer query. This correlated subquery determines if the inner query returns a value that meets the condition of the WHERE clause. If it does, the row identified by the outer query is placed in the record set that will be returned from the complete T-SQL SELECT statement.
select CompanyName, ContactName, Address,
City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in (
select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc
)
order by Region
Here you can see the inner query is a correlated subquery
because it references "OuterC", which is the table alias for the "Northwind.DBO.Customer"
table in the outer query. This inner query uses the "Region" value
to calculate the top two customers for the region associated with the row being
processed from the outer query. If the "CustomerID" of the outer
query is one of the top two customers, then the record is placed in the record
set to be returned.select C.CustomerID, Count(*)*75 Rebate
from Northwind.DBO.Customers C
join
Northwind.DBO.Orders O
on c.CustomerID = O.CustomerID
where Datepart(yy,OrderDate) = '1998'
group by C.CustomerId
having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount))
from Northwind.DBO.Orders O
join
Northwind.DBO.[Order Details] OD
on O.OrderID = OD.OrderID
where CustomerID = C.CustomerId
and Datepart(yy,OrderDate) = '1998'
group by O.OrderId
)
By reviewing this query, you can see I am using a correlated query in the HAVING clause to calculate the total order amount for each customer order. I use the "CustomerID" from the outer query and the year of the order "Datepart(yy,OrderDate)", to help identify the Order records associated with each customer, that were placed the year '1998'. For these associated records I am calculating the total order amount, for each order, by summing up all the "[Order Details]" records, using the following formula: sum(UnitPrice * Quantity * (1-Discount)). If each and every order for a customer, for year 1998 has a total dollar amount greater than 750, I then calculate the Rebate amount in the outer query using this formula "Count(*)*75 ".
create table A(A int, S int)
create table B(A int, B int)
set nocount on
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)
update A
set S = (select sum(B)
from B
where A.A = A group by A)
select * from A
drop table A,B
Here is the result set I get when I run this query on my machine:
A S ----------- ----------- 1 1 2 2 3 3
In my query above, I used the correlated subquery to update column A in table A with the sum of column B in table B for rows that have the same value in column A as the row being updated.
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
And the "Searched Format"
looks like this: CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Where the "input_expression" is any valid Microsoft SQL Server expression, the "when_expression" is the value in which the input_expression is compared, the "result_expression" is the value that will be return for the CASE statement if the "when_expression" evaluates to true, "[...n]" represents that multiple WHEN conditions can exist, the "else_result_expression" is the value that will be returned if no "when_expression" evaluates to true and in the "Searched Format" the "Boolean_expression" is any Boolean express that when it evaluates to true will return the "result_expression". Let me go through a couple of examples of each format to help you better understand how to use the CASE function in a SELECT statement.
create table patients (PatientID int identity,
PatientName varchar(50),
PatientSexCode int)
insert into patients(PatientName, PatientSexCode)
values ('Doe, Jane', 0)
insert into patients(PatientName, PatientSexCode)
values ('Doe, Dick', 1)
insert into patients(PatientName, PatientSexCode)
values ('Doe, Spot', 2)
insert into patients(PatientName, PatientSexCode)
values ('xxxxxxxx', 9)
select PatientName,
case PatientSexCode
when 0 then 'female'
when 1 then 'male'
when 2 then 'unknown'
else 'Invalid PatientSexCode' end as "Patient Sex"
from patients
Here is the output from this T-SQL code:
PatientName Patient Sex --------------------------- ----------------------- Doe, Jane female Doe, Dick male Doe, Spot unknown xxxxxxxx Invalid PatientSexCode
This example shows the syntax in action for a CASE function using the "Simple Format". As you can see the CASE function evaluates the PatientSexCode to determine if it is a 0, 1, or 2. If it is a 0, then "female" is displayed in the output for the "Patient Sex" column. If the PatientSexCode is 1, then "male" is display, or if PatientSexCode is 2 then "unknown" is displayed. Now if the PatientSexCode is anything other than a 0, 1 or 2 then the "ELSE" condition of the CASE function will be used and "Invalid PatientSexCode" will be displayed for the "Patient Sex" column.
select PatientName,
case
when PatientSexCode = 0 then 'female'
when PatientSexCode = 1 then 'male'
when PatientSexCode = 2 then 'unknown'
else 'Invalid PatientSexCode' end as "Patient Sex"
from patients
Note the slight differences between the "Simple" and "Searched" formats. In the "Simple" format I specified the column name for which row values will be compared against the "when_expressions" ,where as in the "Searched" format each WHEN condition contains a Boolean expression that compares the PatientSexCode column against a code value.
Select top 8 ProductName,
case
when QuantityPerUnit like '%Tins%' and
Discontinued = 0
then 'Tins'
when QuantityPerUnit like '%bottles%' and
Discontinued = 0
then 'Bottles'
else 'Not Tins, Not Bottles, or is Discontinued'
end 'Type or Availability'
from Northwind.dbo.Products
order by ProductName
The output for the above command on my server displays the following:
ProductName Type or Availability ----------------------- ----------------------------------------- Alice Mutton Not Tins, Not Bottles, or is Discontinued Aniseed Syrup Bottles Boston Crab Meat Tins Camembert Pierrot Not Tins, Not Bottles, or is Discontinued Carnarvon Tigers Not Tins, Not Bottles, or is Discontinued Chai Not Tins, Not Bottles, or is Discontinued Chang Bottles Chartreuse verte Not Tins, Not Bottles, or is Discontinued
As you can see I'm using a "Searched Format" for this CASE function call. Also, each WHEN clause contains two different conditions. One condition to determine the type (tins, or bottles) and another condition to determine if the product has been discontinued. If the QuantityPerUnit contains the string "Tins" and the Discontinue column value is 0 then the "Type of Availability" is set to "Tins". If the QuantityPerUnit contains the string "Bottles" and the Discontinue column value is 0 then the "Type of Availability" is set to "Bottles". For all other conditions, the "Type or Availability" is set to "Not Tins", "Not Bottles", or is "Discontinued."
select top 5 title,
case
when price < 12.00 then 'Cheap'
when price < 3.00 then 'Really Cheap'
when price > 12.00 and price < 20.00 then 'Average'
else 'Expensive' end 'Price Category'
from pubs.dbo.titles
The output on my machine for this query looks like this:
title Price Category -------------------------------------------------------- ------------ The Busy Executive's Database Guide Average Cooking with Computers: Surreptitious Balance Sheets Cheap You Can Combat Computer Stress! Cheap Straight Talk About Computers Average Silicon Valley Gastronomic Treats Average
If you look at the raw titles table data in the pubs database for the title "You Can Combat Computer Stress!" you will note that the price for this book is $2.99. This price makes both the "price < 12.00" and "price < 3.00" conditions "True". Since the conditions are evaluated one at a time, and the "price < 12.00" is evaluated prior to the "price < 3.00," the "Price Category" for the title "You Can Combat Computer Stress!" is set to "Cheap".
select top 5 title, price
from pubs.dbo.titles
where
case
when price < 12.00 then 'Cheap'
when price < 3.00 then 'Really Cheap'
when price > 12.00 and price < 20.00 then 'Average'
else 'Expensive' end = 'Average'
The output for this query looks like this:
title price -------------------------------------------------------- -------------- The Busy Executive's Database Guide 19.9900 Straight Talk About Computers 19.9900 Silicon Valley Gastronomic Treats 19.9900 Prolonged Data Deprivation: Four Case Studies 19.9900 Sushi, Anyone? 14.9900
Here I only wanted to display books from the titles table in pubs database if the price category is 'Average'. By placing my CASE function in the WHERE clause I was able to accomplish this.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 2 and @Part_ID = 1
Break
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
Here is the output for this
code that contains a BREAK statement inside the inner WHILE loop.Part_Id Category_Id Description ----------- ----------- ----------------------------------------- 1 1 Part_Id is 1 Category_Id 1 2 1 Part_Id is 2 Category_Id 1 2 2 Part_Id is 2 Category_Id 2 2 3 Part_Id is 2 Category_Id 3From this output you can see that no records were inserted for Part_Id = 1 and Category_Id =2 or 3, where as there are records for Part_Id = 2 with all values for the Category_Id column. This is because the IF statement in the inner loop forced the BREAK statement to exit the inner loop. Since there were records generate for Part_Id = 2, shows that the BREAK statement only exited the inner loop and not the outer loop.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 2 and @Part_ID = 1
Continue
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
When you use the CONTINUE
statement you get the following output.----------- ----------- ----------------------------------------- 1 1 Part_Id is 1 Category_Id 1 1 3 Part_Id is 1 Category_Id 3 2 1 Part_Id is 2 Category_Id 1 2 2 Part_Id is 2 Category_Id 2 2 3 Part_Id is 2 Category_Id 3As you can see, when I use the CONTINUE statement only the record with Category_Id = 2 and Part_Id = 1 is missing. This is because the CONTINUE statement does not break out of the inner WHILE loop but only goes back to the top of the WHILE loop without inserting the record. This happens only when Category_Id is 2 and Part_Id is equal to 1. When Part_Id = 1 and Category_Id = 3 the insert statement is still executed.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
If @Category_ID = 3 and @Part_ID = 1
GOTO BREAK_OUT
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
BREAK_OUT:
select * from PART
drop table PART
Here is the output from this
GOTO code:Part_Id Category_Id Description ----------- ----------- ----------------------------------------- 1 1 Part_Id is 1 Category_Id 1 1 2 Part_Id is 1 Category_Id 2Here the GOTO logic stopped the insertion of records into the PART table when @Category_ID = 3 and @Part_Id = 1. This is done by executing the "GOTO BREAKOUT" statement. Note that when this GOTO statement was executed it branched to the label "BREAK OUT:" which can be found following the END statement for the first, outer most WHILE statement.
WHILE <Boolean expression> <code block>Where a <Boolean expression> is any expression that equates to a true or false answer, and the <code block> is the desire code to be executed while the <Boolean expression> is true. Let's go through a real simple example.
declare @counter int set @counter = 0 while @counter < 10 begin set @counter = @counter + 1 print 'The counter is ' + cast(@counter as char) endHere the code executes the WHILE statement as long as the @counter integer variable is less than 10, this is the Boolean expression of the WHILE loop. The @counter variable starts out at zero, and each time through the WHILE loop it is incremented by 1. The PRINT statement displays the value in the @counter variable each time through the WHILE loop. The output from this sample looks like this:
The counter is 1 The counter is 2 The counter is 3 The counter is 4 The counter is 5 The counter is 6 The counter is 7 The counter is 8 The counter is 9 The counter is 10As you can see, once the @counter variable reaches 10 the Boolean expression that is controlling the WHILE loop is no longer true, so the code within the while loop is no longer executed.
declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
Here is the output from the
SELECT statement at the bottom of this nested WHILE loop example.Part_Id Category_Id Description ----------- ----------- ----------------------------------------- 1 1 Part_Id is 1 Category_Id 1 1 2 Part_Id is 1 Category_Id 2 1 3 Part_Id is 1 Category_Id 3 2 1 Part_Id is 2 Category_Id 1 2 2 Part_Id is 2 Category_Id 2 2 3 Part_Id is 2 Category_Id 3As you can see, by using a nested WHILE loop each combination of Part_Id and Category_Id is unique. The code within the first WHILE loop controlled the incrementing of the Part_Id, where as the second WHILE loop set the Category_Id to a different value each time through the loop. The code within the first while loop was executed only twice, but the code inside the second WHILE loop was executed 6 times. Thus giving me 6 sample PART records.