Microsoft Excel is a very powerful tool, using excel we can connect to all most all known data sources available and create a report easily. In this post, we will learn how to connect to a SQL Server database from excel and execute stored procedure in excel with parameter values passed run time.
Lets create a sample stored procedure first, once created follow the steps to configure the same in excel.
CREATE PROC usp_ExportExcelSample (@name VARCHAR(100) = '' ,@type VARCHAR(20) = '' ) AS BEGIN SELECT * FROM sys.objects a WHERE a.is_ms_shipped = 1 AND a.name = COALESCE(NULLIF(@name, ''), a.name) AND a.type_desc = COALESCE(NULLIF(@type, ''), a.type_desc) END
Step 01: RUN → EXCEL
Step 02: Choose blank workbook
If you have already a connection created earlier then you can use the same otherwise you can create a new one.
A new connection is created with name SQL CONNECTION
Pass some values to the parameters here for the first execution, don’t worry we will make them dynamic later.
I am leaving 2 rows above and loading data from 3rd row, that is just for my convenient because I am going to use those two rows as a placeholder for my stored procedure parameters.