How do I call a stored procedure with arguments using sqlcmd.exe?

Question

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.

Remus Rusanu · Accepted Answer · 2011-06-19 23:47:57Z

19

sqlcmd.exe supports variable substitution and parameters via the /v argument, see Using sqlcmd with Scripting Variables. For example:

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.

edited Jun 19, 2011 at 23:47

answered Jun 19, 2011 at 21:34

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

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

1 Comment

M D Over a year ago

Thanks, that helps a lot. It should be a lower-case 'v' so I corrected that for you, otherwise good.

Aleksey Cherenkov · Accepted Answer · 2015-01-09 16:04:45Z

4

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.

Example:

$myparam = "..." Invoke-Sqlcmd -Query "EXEC sp_myproc $myparam" -ServerInstance "MyComputer\MyInstance"

answered Jan 9, 2015 at 16:04

Aleksey Cherenkov

1,49523 silver badges27 bronze badges

Collectives™ on Stack Overflow

How do I call a stored procedure with arguments using sqlcmd.exe?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related