VBScript to Take SQL Query Results and Pass to SP

[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'

 

[PHV]

What about this ?
EXEC spUpdTerminatedStatus 'T','HRID','03/20/2012',(SELECT ID from zSYSUSER WHERE HRID = XXXXX)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dkel22]

Doesn't work PHV it is looking for an Int value and keeps giving the "error converting data type varchar to int

 

[PWise]

how about passing all the following to Sql Server

Declare @ID int SELECT @ID=ID from zSYSUSER WHERE HRID = XXXXX
EXEX spUpdTerminatedStatus 'T','HRID','03/20/2012',@id

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

Doesn't work PHV it is looking for an Int value and keeps giving the "error converting data type varchar to int"

note you are trying to pass a string and Sql Server is telling you I am expecting a int

 

[dkel22]

Your suggestion works great when I am testing in SQL management PWise, thank you very much! I am a novice with scripting and SQL so that is very helpful.

Do you happen to have any powershell experience? Ideally I would like to take your suggestion and make a powershell script from it. I am sure it is not all that difficult to convert.

 

[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?