Creating the Config Function
Start course
Difficulty
Intermediate
Duration
46m
Students
1893
Description

In this lecture we 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.

Transcript

- [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.

About the Author
Students
21255
Courses
72
Learning Paths
14

Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a  Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.