Powershell SQL sp with Query Results

[dkel22]

I am trying to do something like the example below. I want to run a sql query and then execute a sp using the query result.


QUERY:
SELECT ID from zSYSUSER WHERE HRID = XXXXX
-----> This returns the result of a zSYSUSERID of YYYYY

SP:
EXEX spUpdTerminatedStatus 'T','HRID','03/20/2012','YYYYY'

 

[dkel22]

Where I've gotten so far. I am using the following to try and save the query results as a variable and then pass to the stored procedure.

$x = invoke-sqlcmd -query "SELECT ID from zSysUser WHERE HRID = 1111" -database DATABASENAME -serverinstance SERVERNAME

This will save the variable fine, but I believe it is saving the row now the ID

Then to run the SP

invoke-sqlcmd "EXEC spUpdTerminatedStatus 'T','HRID','DATE','$x'" -database DATABASENAME -serverinstance SERVERNAME

This will not run because it is saving $x as system.data.datarow and not system.int32 which the SP is looking for an int for $x

Thoughts?

 

[58sniper]

How are you casting that variable?
Try

[int]$x = invoke-sqlcmd -query "SELECT ID from zSysUser WHERE HRID = 1111" -database DATABASENAME -serverinstance SERVERNAME

and if that doesn't work

$x = invoke-sqlcmd -query "SELECT ID from zSysUser WHERE HRID = 1111" -database DATABASENAME -serverinstance SERVERNAME 

invoke-sqlcmd "EXEC spUpdTerminatedStatus 'T','HRID','DATE','[int]$x'" -database DATABASENAME -serverinstance SERVERNAME

Do you have your Tek-Tips.com Swag? I've got mine!

Stop by the new Tek-Tips group at LinkedIn.

 

[dkel22]

It was giving me issues with converting datarow to int32 with both your methods. My workaround, although probably not optimal was

[int]$y = $x[0]
then calling the SP with $y

 

[58sniper]

Okay. Glad you got it to work.

Do you have your Tek-Tips.com Swag? I've got mine!

Stop by the new Tek-Tips group at LinkedIn.