Variables and flow control in automation for Toad for SQL Server

Hello, folks. My name is Robert Pound. And I am a Product Technologist for Quest. In this video, we will discuss variables, as well as the IF activity in Toad for SQL Server automation. So before we discuss automation, let's first walk through a variable in the Editor. So I have an Editor open and I have a statement that has a variable in it. So you declare variables with the colon and then the variable name. So if I run this statement, it will prompt me for an input. And I can input any value that I choose and click OK. And it will go ahead and filter the statement with that particular value. And you can see in the output, that it's all order quantity of 12. So when we moved to automation, I have this same statement added into an activity in the automation, but you can see that it's throwing an error. So it's basically saying we need to bind this variable to the set variable activity. So if I add a set activity-- let me go ahead and move this up-- we need to go ahead and declare that variable that we have in the statement. So let me go ahead and add user quantity and set the value to number. And we'll prompt when run, that way the user is asked what value they want to input. And we're going to go ahead and add another value as well. So the second value is going to be the max quantity for this particular column. So I'm going to make a SQL statement. Let me go ahead and copy over the statement that I had previously. I'll copy this in. And what this is going to do is find the max value for that column, like I said. So now we have max quantity and user quantity. So what I'm going to do next is add an IF condition here so that we can run different activities depending on the input itself. So let me move this in to this IF condition here. So once you add in IF, each one of the conditions you can select it, and then add an expression. So you have at your disposal variables, operators, date functions, math functions, string functions. I'm going to stick with variables for this activity. So let me go ahead and choose User quantity. This is the input that we're prompting for. And if this value is less than or equal to our max quantity, the largest value in the column, then we will go ahead and execute this statement. And let me go ahead and move to a fully fleshed out example. So here we can see we have the same variables. And then for each one of the IF statements, anything higher than the max, we'll run a statement, we'll log a value saying that the input was too high, we'll execute the full table. And then it will send it out with an error message. In this case, the value is good. We've added a second part to the IF condition that's saying greater than 0. And then we'll filter based on the user input, and then email that out as well. And then finally negative values, anything less than 0. And if you're wondering, you can add branches by simply right clicking and choosing Add branch. And again, we're just going to log a message and then email it out-- not pulling the data for this particular branch. And for the filtered result, I also added in the user input into the file name. So if you can see when I hover over it, the file name is going to be toadexpert_input, and then the user quantity. So if I put in for instance, 12 like I did previously, it would be userinput12_workorder. So you have a wide variety of things you can do with the variables in automation. I've also added the user input into the emails as well. So any time you dereference a variable and you want to output it to an email, to your log, et cetera-- you just use the double hash-- one on each side-- and that will get you the current value of the variable. So with that, let me go ahead and run this automation a couple of times just to show you it working. And that will pretty much cover my demo. So let's do an outlandishly large number and click OK. And it's exported to CSV, and it's sent out an email. Open up my email-- yep, it's attached with all the data in it. And then we'll run it one more time with an actual feasible value. We'll just say 55 and click Enter. And it's named it input 55. And if I check my email again, I get the new email with the new filtered data set. And that's all there is to it. Hopefully this helped you understand a little bit more about variables and IF conditions in automation for Toad for SQL Server. For more information on this product visit www.Quest.com/products/Toad-for-SQL-Server or you can check out the community-driven innovation going on at www.Toadworld.com/products/Toad-for-Sql-Server. Thank you and have a great day.