Set Value to a variable – ISNULL or EMPTY

Here is the sql script to set value to a variable, when the value of the variable is NULL or ” (empty).


--Declare a @date variable with default value of NULL

DECLARE @date DATE = NULL

--Set yesterday date if the @date variable value is NULL
SET @date = ISNULL(NULLIF(@date, ''), GETDATE() - 1)
PRINT @date

--Set @date variable value as '' (empty)

SET @date = ''

--Set yesterday date if the @date variable value is NULL
SET @date = ISNULL(NULLIF(@date, ''), GETDATE() - 1)
PRINT @date

--Set @date variable value as a specified date

SET @date = '20160927'

--Set yesterday date if the @date variable value is NULL

--This will not set yesterday value to @date variable, instead it will take the above set value
SET @date = ISNULL(NULLIF(@date, ''), GETDATE() - 1)
PRINT @date

 

Advertisements

Execute stored procedure in Powershell and get print statements output on host

I had a requirement to execute the stored procedure from Powershell. I cannot use SQLCMD as it was not installed on the application server. I had to use Microsoft .Net framework library to execute the stored procedure.

The below code snippet was used to execute the stored procedure and it works just fine.

$connectionString = "Data Source=SRV01; Integrated Security=SSPI; Initial Catalog=DB01"
$id = 12345
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$sqlCommand = "EXEC [usp_get_details] $id, 0"
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$connection.Close()

But the PRINT messages from the procedure were not displayed on the screen, which are required to identify the progress or status of the procedure execution. I have searched in internet and found useful post by Jonathan at https://www.sqlskills.com/blogs/jonathan/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell/

After implementing the event handler as described in the above link the code looks like below and works as expected.

$connectionString = "Data Source=SRV01; Integrated Security=SSPI; Initial Catalog=DB01"
$id = 12345
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)

## Attach the InfoMessage Event Handler to the connection to write out the messages
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };
$connection.add_InfoMessage($handler);
$connection.FireInfoMessageEventOnUserErrors = $true;

$sqlCommand = "EXEC [usp_get_details] $id, 0"
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
$connection.Close()