- [Instructor] We'll use a series of if statements to construct a four-character string that will represent the 18-canister configuration. Configuration is the four integer values stored in the ATM table. If the ATM has one $50 canister and three $20 canister, the string will be 5222. I'm doing this because we need to pass that configuration into a function that we will build to allocate the denominations to the canisters based on our business roles. We're going to create a function that returns a table containing the canister split for a particular denomination. It will return a table with one integer field that we will call CassetteAmount. The function will take the total denomination amount in the amount field, capacity of the cassette, the value of a brick and a flat for that particular denomination and the cassette string that we have previously built plus the code of that cassette. The code will be five for 50s, two for 20s or one for 10s. The table definition that the function returns is defined in the return segment of the function declaration. 

Inside the function, we use that table in the declaration as we would a normal table, that is we insert values into it that we wanna be returned. Let's have a look at the output from this function. So here's our function here and we can see that we have insert statements insert into our cassette amounts which we have defined up here as the return table, so when we do a select from this function, we get a small dataset telling us that the $80,000 that is of 20s, that should be split into three cassettes, each with a capacity of 40,000 and 20s come in bricks of 20,000 and flats of 2,000, so the split is 20/40/20. Now that we have our function returning the canister distribution, you just call it with the appropriate parameters and then cycle through the resulting dataset with a cursor using Dynamic SQL to create our update statements. As the function returns the correct number of canisters for that denomination, we just increase the CassetteNumber and use that case as a var chart update the correct day field. For each denomination amount, the ATM supports would perform this process. The function will be executed if the denomination amount is greater than zero and the cassette for that denomination exists in the configuration. When we are finished with our cursor, we must close it and then deallocate it. Because we will be performing this operation multiple times for different denominations, we can refactor this code into a stored procedure. As you can see here, we use this CassetteNumber and we cast it as a varchar here and append it to the amount along with our day of week, so in this case, we will end up with a field of fri for Friday, one, amount_ and whatever the cassette is, one, two or three or two, three, four. We'll get assigned the denomination, hyphen the amount, so in this case, it could be 20-20000 or 20-40000 or maybe 50-100000.