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/de...mdaobj01_7.asp
--
"Sam" wrote in message ...
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" wrote in message
...
you don't explain how you connected tot he DB, but it
sounds like you're using Query Analyser.
A VIEW works much like a table, though it allows a
greater degree of complexity. A VIEW is also, a "view"
like a table...hence the rather obvious name.
Stored Procedures on the other hand need executing...much
a you would a procedure in VB/VBA
SP's offer a high degree of flexibility - its rather like
writing VB code, but using TSQL instead of VB, but its
very similar.
In Query Analyser type in the SP name, followed by any
parameters then hit F5
In VB/VBA once you've set an ADO connection to the
database, you can populate a recordset by Opening it with
some sql code
eg
"SELECT * FROM MyTable"
or if it's an SP
"usp_My_Stored_Proc"
if the sp returns a table, then your recordset will be
populated
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
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
.