Scripting out procs and user defined functions with PowerShell

I decided to take SonarCube for a spin to run some static code analysis. In order to do that I needed some code that I can display here without breaking laws.

I a real world, you would just point SonarCube to your code repository, but in order to show you how to run it so that you can follow along, I decided to go this route

I decided to use the AdventureWorks database to script out some procs and functions. If you want to follow what I am doing here, grab the backup from here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

On your C drive create a folder named DB, in the DB folder create 2 folders, one name data and the other log

To restore the DB you can run this command (assuming your backup is in the c:\temp directory

USE [master]
RESTORE DATABASE [AdventureWorks2017] 
FROM  DISK = N'C:\temp\AdventureWorks2017.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2017' 
TO N'C:\DB\DATA\AdventureWorks2017.mdf',  
MOVE N'AdventureWorks2017_log' 
TO N'C:\DB\LOG\AdventureWorks2017_log.ldf',  
NOUNLOAD,  STATS = 5

 Now It's time to script out the procs and user defined functions

But first we need a place to store them

In my case I decided to create 2 folders in the c:\temp directory
StoredProcedures
UserDefinedFunctions

Now it's time to script out the procs and user defined functions, I will be using Powershell from that, you will need to install the sql server module, you can download it here

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

Follow the instruction if you get any errors.

Now it's time to run the script, you will need to run powershell as an administrator

You will need to change Machinename\sql2019 to your machine and instance name

The script is simple.. it's for a one time use.. if you need to run the script repeatedly, you probably want to make it so you can rerun it for different databases and servers, so no hardcoding  :-)

Import-Module SqlServer -Version 21.0.17279

cd SQLSERVER:\SQL\MAchinename\sql2019\Databases\AdventureWorks2017\StoredProcedures



foreach ($tbl in Get-ChildItem  )

{
$k="C:\Temp\StoredProcedures\" + $($tbl.Schema) + "." + $($tbl.name) + ".SQL"
$tbl.Script() > $k
}

cd ..\UserDefinedFunctions

foreach ($tbl in Get-ChildItem  )

{
$k="C:\Temp\UserDefinedFunctions\" + $($tbl.Schema) + "." + $($tbl.name) + ".SQL"
$tbl.Script() > $k

That's all for the script.. if it ran successfully, you should see a bunch of procs and user defined functions in their directories.  Here is a view of the functions

Next up..installing SonarQube, SonarQube Scanner and the sonar-tsql-plugin

Defaults with parameters in SQL Server stored procedures gotcha

I was talking with someone today and this person told me that the stored procedure doesn't work as expected



Here is what the simplified code looked like

CREATE PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int = 1,
@SomeThirdParam int
AS
SELECT @SomeOtherParam
GO

The person told me that when calling the proc and passing in NULL, the value would be NULL, he expected the value to be 1 since that is the default. Running the code like this will return the value NULL

EXEC prTest 1,null,3

In order to omit this parameter you can't be passing it in. If your parameter is the last one then you can just omit the value and pass in the first 2 parameters. If your parameter is not last,  then use named parameters instead of positional parameters. if you run the query like this, you will get back the value 1

EXEC prTest @SomeParam = 1,@SomeThirdParam = 3

Here is an example where the last parameter has a default

ALTER PROCEDURE prTest
@SomeParam int,
@SomeOtherParam int ,
@SomeThirdParam int =3
AS
SELECT @SomeThirdParam
GO

Running that proc with just the first 2 values will return the default

EXEC prTest 1,2

So to recap..... a default will only be applied when the parameter is not passed in, if you pass in a NULL, which is technically the absence of a value, NULL will be used

Did you know you can pass false and true to SQL Server bit parameters?

I noticed a stored procedure with a bit parameter and the default was false, I have never seen this before in SQL Server. You can do this in a language like c# but not in SQL Server right?

Let's take a look

Create this proc

create procedure prTest
@BitTrue bit = true,
@BitFalse bit = False,
@BitNotFalse bit = -2
as
Select @BitTrue as BitTrue, @BitFalse as BitFalse,
@BitNotFalse as BitNotFalse
GO

Now execute the sored procedure

EXEC prTest

BitTrue BitFalse BitNotFalse
1 0 1


As you can see true gets converted to 1, false gets converted to 0 and any number that is not 0 will be converted to 1 as well

What happens when you pass in false for the parameter that had a default of true?

EXEC prTest @BitTrue = false

BitTrue BitFalse BitNotFalse
0 0 1



How about variables..can you use true and false with bit datatype variables?
Let's try it out

DECLARE @BitTrue bit 
SET @BitTrue = true

Msg 207, Level 16, State 1, Line 2
Invalid column name 'true'.

DECLARE @BitTrue bit 
SET @BitTrue = false

Msg 207, Level 16, State 1, Line 3
Invalid column name 'false'.


As you can see you can  use true and false with parameters in a proc but not with variables.