How can I grab data from a SQL Server stored procedure

[Sam]

Hi,

I want to place an excel sheet on our corporate portal site that connects to
a stored procedure on SQL Server 2000, passess two parameters and grabs
data.

When I connected to SQL Server, I was able to see tables and views but not
stored procedures. How can I connect to the DB and grab data from a stored
procedure? Thanks,

Sam

 

[Sam]

Hey Patrick,

Thanks for your response. I'm connecting to the data source through a DSN.

My parameters will have different values every time user runs the SP. The
question is will I be able to pass these paramaters to Excel so that it will
pass them to the SP and receive the results

Thanks,

Sam

 

[Patrick Molloy]

That's a bit confusing.
A DSN is simply th econnection.
You pass parameters TO the database SP FROM Excel

SQL = "sp_MyStoredProc " & param1 [ & "," & Param2 ]...

OR

SQL = "SELECT * From " & sTableName
SQL = SQL & " WHERE " & param1 & "='" & condition1 & "';"


Patrick Molloy
Microsoft Excel MVP

 

[onedaywhen]

Using ADO, you can use a Command object's Parameters collection.

Alternatively, you may be able to use the Connection object directly.
I've seen evidence (but can't recall where!) that this approach has
performance benefits. However the syntax seems a bit hit-and-miss. For
details see the section 'Execute a command as a native method of a
Connection object' in the documentation:-

ADO 2.8 API Reference: Connection Object:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaobj01_7.asp

--