Executing Stored Procedures with Text Parameters from Excel

 Last reviewed: November 3, 1994
Article ID: Q73288
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.1 and 3.0
  • Microsoft Excel for OS/2, version 3.0

SUMMARY

You can execute SQL Server-stored procedures from Microsoft Excel in a custom macro by using the macro functions provided in the add-in QE.XLA or by sending commands to Q+E via DDE (Dynamic Data Exchange).

Parameters passed to the stored procedure must be placed after the name of the procedure and separated with a single space.

MORE INFORMATION

It is important that when you pass a text parameter that you enclose it in double quotation marks. Because the whole query passed from Microsoft Excel is in double quotation marks and you may not place a set of double quotation marks inside another set, you must use two sets of single quotations marks around the text parameters when executing a stored procedure from Microsoft Excel.

The two examples below execute the stored procedure SP_HELPDB to obtain information on the example database PUBS. The information returned from the server is pasted to Sheet1.

The first example uses the macro functions supported by the QE.XLA. For this to work correctly, you must first open QE.XLA from the \XLSTART\QEMACRO subdirectory of your Excel directory.

Example Using Functions Supplied by the QE.XLA

A1 =INITIATE("qe","system") A2 =ACTIVATE("sheet1") A3 =DB.LOGON("SQLServer") A4 =DB.SQL.QUERY(2,"sp_helpdb ''pubs''",1,FALSE) A5 =RETURN()

NOTE: In A4, there are two single quotation marks before the parameter pubs and two single quotation marks, followed by a double quotation mark, after pubs.

Example Using DDE Functions

A1 chan=INITIATE("qe","system") A2 =EXECUTE(chan,"[logon('SQLServer')]") A3 =ACTIVATE("Sheet1") A4 =EXECUTE(chan,"[open('execute sp_helpdb ''pubs''')]") A5 =EXECUTE(chan,"[copy.special(true,false,2,1)]") A6 =PASTE() A7 =TERMINATE(chan) A8 =RETURN()

NOTE: There are two single quotation marks before the parameter pubs and three single quotation marks after.

REFERENCES

"Q+E for Microsoft Excel User's Guide," version 3.0, pages 142-143

KBCategory: kbother
KBSubcategory:

Additional reference words: 2.1 2.10 3.0 3.00 qe sql.query
db.sql.query

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.