I'm attempting to run some SQL through Powershell, using a wrapper function in PS to run the SQL command. As well, my SQL is in its own independent file and not hard-coded inline to the Powershell script, which would make what I'm trying to do trivial. What I need is to be able to have SQL Server do is to access the Powershell variable and use it in the query I'm sending. xp_cmdshell is coming up as a potential option to do this with, but I'd sooner not expose myself to the security vulnerabilities there if I don't have to.
Here's some pseudocode to get my idea across easier:
$var = 1
$query = query.sql
$con = connection(server_connection_params)
$command = New-Object System.Data.SqlClient.SqlCommand $sqlCommand,$connection
SELECT * FROM table WHERE field = $var;
Is there any way I could access
$var in this fashion given how I'm calling the SQL in Powershell?