Stored procedures are coded database objects that allow programmers to retrieve data from tables. Some companies require you to retrieve data and export it to an Excel spreadsheet. You can do this in a SQL stored procedure. The procedure retrieves data using a SQL query and sends the results directly to your Excel spreadsheet. Creating a stored procedure allows you to execute the exportation of records at any time without recreating the code.
Create your stored procedure name. Every stored procedure requires a name. The following code starts a stored procedure:
create proc newProc ()
Insert the stored procedure code that queries the database and exports data to Excel. The code to create the export process only requires one SQL statement. To export data from a table to Excel:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\theExcelFile.xls;', 'select Name from [Sheet1$]') select FirstName, LastName from Customer
The first line of code opens the Excel database and sets the driver, which is Jet.OLEDB for Excel spreadsheets. The next section specifies the path for the spreadsheet and the sheet. The first sheet in an spreadsheet is \"Sheet1$.\" The final select statement is the records retrieved from the database to export.
Press the F5 key to execute the statement. This saves the stored procedure in your database. To execute the stored procedure and export data, enter the following code into the SQL editor:
- Photo Credit number background image by kuhar from Fotolia.com
How to Create a Spreadsheet in Microsoft PowerPoint
PowerPoint is a type of presentation software developed by Microsoft. There are many ways you can modify your presentation. You can add...
Tutorial on SQL Triggers & Stored Procedures
Stored Procedures and Triggers within a database are similar constructs. They can both perform the same SQL statements. The biggest difference between...
How to Call an SQL Stored Procedure Using MS Access VBA
Knowing how to call a Microsoft SQL Server stored procedure from Microsoft Office Access using Visual Basic for Applications (VBA) can save...
How to Create or Replace Procedures in MS SQL
The Microsoft SQL Server database software lets you create stored procedures, which are functions saved on the server. You can call these...
How to Convert a SQL File to CSV
SQL files are stored procedures saved to text file format. You open these files on a SQL Server, and the file's code...