November 13, 2009

Executing a Stored Procedure using SQLCMD

Here’s how to execute a SQL Server Stored Procedure using SQLCMD. For demonstration purposes, I will be executing the ‘CustOrderHist’ Stored Procedure in the Northwind database. This procedure accepts the CustomerID and returns a list of orders placed by the Customer.

Open your Command Prompt. I have a named instance and use Windows Authentication, so I will be connecting to SQL Server using the following statement:

sqlcmd -S <ComputerName>\<InstanceName>

In my case, it is:    sqlcmd -S SUPROTIM-PC\SUPROTIM2

Now enter the following commands:

:Setvar CustomerID ALFKI
EXEC dbo.CustOrderHist $(CustomerID)
Go

The output is as shown below:

image


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

2 comments:

ESTEBAN ALVINO Q. said...

but, if you have more than a database
?

Burtey said...

How can i print the list of databases in my current instance using SQLCMD?