I need to call a stored procedure and pass arguments in from Powershell. I think the best option is to use sqlcmd.exe but I'm not sure how to pass arguments to the stored proc using this tool.
我需要调用存储过程并从Powershell传递参数。我认为最好的选择是使用sqlcmd.exe，但我不知道如何使用此工具将参数传递给存储过程。
2 个解决方案
#1
12
sqlcmd.exe supports variable substitution and parameters via the /v argument, see Using sqlcmd with Scripting Variables. For example:
sqlcmd.exe通过/ v参数支持变量替换和参数，请参阅使用带有脚本变量的sqlcmd。例如：
sqlcmd -E -d <mydb> -Q "exec usp_myproc @variable=$(myparam)" /v myparam=1
will invoke the procedure passing the value 1 to the script to be substituted for the variable $(myparam). Note that sqlcmd variable substitution is a string replacement of $(variable) which occurs in sqlcmd, befor the batch (request) is sent to the SQL Server.
将调用将值1传递给脚本的过程以替换变量$（myparam）。请注意，sqlcmd变量替换是$（变量）的字符串替换，它发生在sqlcmd中，因为批处理（请求）被发送到SQL Server。
#2
2
The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a Windows PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.
Invoke-Sqlcmd cmdlet允许您在Windows PowerShell环境中运行sqlcmd脚本文件。您可以使用Invoke-Sqlcmd完成使用sqlcmd可以执行的大部分操作。
Example:
例：
$myparam = "..." Invoke-Sqlcmd -Query "EXEC sp_myproc $myparam" -ServerInstance "MyComputer\MyInstance"
$ myparam =“...”Invoke-Sqlcmd -Query“EXEC sp_myproc $ myparam”-ServerInstance“MyComputer \ MyInstance”
#1
12
sqlcmd.exe supports variable substitution and parameters via the /v argument, see Using sqlcmd with Scripting Variables. For example:
sqlcmd.exe通过/ v参数支持变量替换和参数，请参阅使用带有脚本变量的sqlcmd。例如：
sqlcmd -E -d <mydb> -Q "exec usp_myproc @variable=$(myparam)" /v myparam=1
will invoke the procedure passing the value 1 to the script to be substituted for the variable $(myparam). Note that sqlcmd variable substitution is a string replacement of $(variable) which occurs in sqlcmd, befor the batch (request) is sent to the SQL Server.
将调用将值1传递给脚本的过程以替换变量$（myparam）。请注意，sqlcmd变量替换是$（变量）的字符串替换，它发生在sqlcmd中，因为批处理（请求）被发送到SQL Server。
#2
2
The Invoke-Sqlcmd cmdlet lets you run your sqlcmd script files in a Windows PowerShell environment. Much of what you can do with sqlcmd can also be done using Invoke-Sqlcmd.
Invoke-Sqlcmd cmdlet允许您在Windows PowerShell环境中运行sqlcmd脚本文件。您可以使用Invoke-Sqlcmd完成使用sqlcmd可以执行的大部分操作。
Example:
例：
$myparam = "..." Invoke-Sqlcmd -Query "EXEC sp_myproc $myparam" -ServerInstance "MyComputer\MyInstance"
$ myparam =“...”Invoke-Sqlcmd -Query“EXEC sp_myproc $ myparam”-ServerInstance“MyComputer \ MyInstance”