Declare SQL Variables in Powershell

Question

I have a SQL Query that I am trying to add to Powershell so that I can setup a routine to export a CSV file. The SQL works great:

declare @firstdayfilter datetime;
declare @lastdayfilter datetime;
declare @SalesDate  date;

set @firstdayfilter = (select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0));
set @lastdayfilter = (SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE()));
set @SalesDate = (select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0));

select @firstdayfilter ,@lastdayfilter ,@SalesDate

When I move this into a Powershell file, I get an error when I try to declare the variable. I've tried many different iterations to no success. I am a noob at Powershell.

Here's what I had originally (for the first field).

$FirstDayFilter = @"
 Declare @firstdayfilter datetime ;
 set @firstdayfilter = (select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0));
"@

$SqlQuery = @"
SELECT 
 $FirstDayFilter
"@

This is the error message I get:

Exception calling "Fill" with "1" argument(s): "Incorrect syntax near the keyword 
'Declare'."
At S:\Common\Active IT Projects\Projects\OrionSales\OrionCPIMTDSalesReport.ps1:277 char:5
+     $SqlAdapter.Fill($DataSet) #| Out-Null
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

So what am I missing? I know other fields work. For example if I set $FirstDayFilter = "Test" it works.

Rich Moss · Accepted Answer · 2019-09-17 17:04:04Z

3

This code:

$SqlQuery = @"
SELECT 
 $FirstDayFilter
"@

Produces this string:

SELECT 
Declare @firstdayfilter datetime ;
 set @firstdayfilter = (select DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0));

...which is not valid SQL. Maybe you meant:

$SqlQuery = @"
 $FirstDayFilter
 SELECT @FirstDayFilter
"@

answered Sep 17, 2019 at 17:04

Rich Moss

2,3941 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh McGee Over a year ago

That was my problem. Thank you.

Collectives™ on Stack Overflow

Declare SQL Variables in Powershell

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related