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

sql-server stored-procedures powershell sqlcmd
Martin Doms picture Martin Doms · Jun 19, 2011 · Viewed 37.8k times · Source

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.

Answer

Remus Rusanu picture Remus Rusanu · Jun 19, 2011

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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Search text in stored procedure in SQL Server

I want to search a text from all my database stored procedures. I use the below SQL: SELECT DISTINCT o.name AS Object_Name, o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_…

Read More
SQL Server - SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a sproc is necessary. Is it possible to select the output by …

Read More