Getting Started With PowerShell Variables
Yesterday we laid the ground work for PowerShell Week. Today we will learn about Variables in PowerShell. You will notice that PowerShell variables work very differently from SQL Server variables.
The evolution of variables in SQL Server is pretty straight forward. In SQL Server 2000 you had to DECLARE a variable as a specific type in one statement and then SET the value of the variable in another statement, like this: DECLARE @SQLvariable VARCHAR(12) SET @SQLvariable = ‘OldFeature’. By the time SQL 2008 hit the streets we could DECLARE and SET a variable in one statement like this: DECLARE @SQLvariable VARCHAR(12) = ‘NewFeature’ . PowerShell evolves variables to the next logical step. You no longer need to declare or type your variable. Instead PowerShell uses the value of the variable to infer it’s type. If a variable does not alreayd exist PowerShell simply creates it for you. In PowerShell our statement looks like this: $SQLvariable = ‘NewFeature’. (Beginer tip: $ identifies variables in PowerShell just like @ identifies them in SQL Server.)
Since it doesn’t work in SQL let’s try it in PowerShell: $SQLvariable = ‘NewFeature’. OK so we loaded data into a variable, ‘big deal’ right? How do we know that we actually loaded it and more importantly what datatype is it? Well in PowerShell you can simply call the variable to get its value. (Another beginer tip: you don’t need to use a keyword like SELECT for this, just the variable name: $SQLvariable.) What if you just want to determine the datatype? All you have to do is tack on the .GetType() Method: $SQLvariable.GetType().
$SQLvariable = 'NewFeature' $SQLvariable $SQLvariable.GetType()
When you run this here’s what your result will be:
As I alluded to earlier, another feature of PowerShell is that it remembers your variables for you, even between executions. Basically as long as you don’t end your session the variable, it’s datatype, and its value will persist. You will see later than you can also set a variable in one script and consume it in another.
Let’s try this again with a new variable and set it to a different value:
$PoShvariable = 123 $PoShvariable $PoShvariable.GetType()
What happens if once the datatype is set we try to set it to a different, incompatible datatype? It’s going to explode right? Nope, you’ll have to throw something more difficult than that at it. Give this a try:
$PoShvariable.GetType() $PoShvariable = 'NewFeature' $PoShvariable $PoShvariable.GetType()
Since PowerShell relies on .NET a variable could be any datatype in .NET so if you’re sitting inside the AdventureWorks database you can see for yourself, just run this:
$MyTable = get-item TABLESProduction.TransactionHistory $MyTable.GetType() $MyStoredProc = get-item StoredProceduresHumanResources.uspUpdateEmployeePersonalInfo $MyStoredProc.GetType()
Now earlier I mentioned that PowerShell keeps track of and remembers the variables that you previously declared. So how do you see what variables are already there? Just run this command and you can see every variable you have running in the session, even the internal ones.
Variable
In the list of items that get returned you’ll see one called Error that holds the last error message that occurred in your session. This handy feature can help you when you have to debug your own work.
Is that all there is to know about variables in PowerShell? Not hardly, but I think that’s a good stopping point for this post. More on variables later.
