Execute SQL procedure with parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a SQL procedure that uses 2 dates (fromdate, todate) as variables
I can sucessfully run the SQL Procedure when i supply the fromdate & todate within MS Query
However, MS Query won't accept the ? as a parameter marker for the Fromdate, Todate

I want individuals users to key in their own fromdate & todate based on what range of data they want to view

MS Query Help indicates this should work by replacing the actual Fromdate & ToDate with question marks (?), but it doesn't. Is there a setting or an option that needs to be turned on?
Or some other way of running a procedure by supplying the parameters from within excel?
 
Hi TroyS,

This should work - can you reply with the SQL source for your query?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
You may anyhow be better off using a VBA routine to get input of the
date parameters yourself e.g. you would be able to validate the input
(which MS Query doesn't do), you could use a date picker control, etc.

--

Jake Marx said:
Hi TroyS,

This should work - can you reply with the SQL source for your query?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

i have a SQL procedure that uses 2 dates (fromdate, todate) as
variables.
I can sucessfully run the SQL Procedure when i supply the fromdate &
todate within MS Query.
However, MS Query won't accept the ? as a parameter marker for the
Fromdate, Todate.

I want individuals users to key in their own fromdate & todate based
on what range of data they want to view.

MS Query Help indicates this should work by replacing the actual
Fromdate & ToDate with question marks (?), but it doesn't. Is there a
setting or an option that needs to be turned on? Or some other way of
running a procedure by supplying the parameters from within excel?
Click to expand...
Click to expand...
 
Troy

Also note that you can't enter the ? in the UI, only in SQL view. In the
UI, you must enter a prompt string surrounded by brackets []. Otherwise the
UI interprets the ? as a literal string - i.e. it changes to '?'.

You may have already been doing that, but it wasn't clear from your post, so
I thought I'd mention it.

See here for more on parameters
http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

TroyS said:
i have a SQL procedure that uses 2 dates (fromdate, todate) as variables.
I can sucessfully run the SQL Procedure when i supply the fromdate & todate within MS Query.
However, MS Query won't accept the ? as a parameter marker for the Fromdate, Todate.

I want individuals users to key in their own fromdate & todate based on
Click to expand...
what range of data they want to view.
MS Query Help indicates this should work by replacing the actual Fromdate
Click to expand...
& ToDate with question marks (?), but it doesn't. Is there a setting or an
option that needs to be turned on?
Or some other way of running a procedure by supplying the parameters from
Click to expand...
within excel?
 
I've tried this

DECLARE @RC in
DECLARE @BeginDate datetim
DECLARE @EndDate datetim
SELECT @BeginDate = '1/1/04
SELECT @EndDate = '1/31/2004
EXEC @RC = [TWO].[dbo].[CTL_RevenuebyLead_proc] @BeginDate, @EndDat

and I've tried this

EXEC CTL_RevenuebyLead_proc ?,

both examples are from the SQL Statement windo

----- Jake Marx wrote: ----

Hi TroyS

This should work - can you reply with the SQL source for your query

--
Regards

Jake Mar
MS MVP - Exce
www.longhead.co

[please keep replies in the newsgroup - email address unmonitored


TroyS wrote
 
Hi TroyS,

This worked for me:

EXEC CTL_RevenuebyLead_proc @BeginDate=?, @EndDate=?

Just using ? didn't work, but using it in conjuction with the parameter name
seemed to do the trick.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I've tried this:

DECLARE @RC int
DECLARE @BeginDate datetime
DECLARE @EndDate datetime
SELECT @BeginDate = '1/1/04'
SELECT @EndDate = '1/31/2004'
EXEC @RC = [TWO].[dbo].[CTL_RevenuebyLead_proc] @BeginDate, @EndDate

and I've tried this:

EXEC CTL_RevenuebyLead_proc ?, ?

both examples are from the SQL Statement window

----- Jake Marx wrote: -----

Hi TroyS,

This should work - can you reply with the SQL source for your
query?

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]

i have a SQL procedure that uses 2 dates (fromdate, todate) as
variables.
I can sucessfully run the SQL Procedure when i supply the
Click to expand...
fromdate &> todate within MS Query. > However, MS Query won't
accept the ? as a parameter marker for the > Fromdate, Todate.actual > Fromdate & ToDate with question marks (?), but it
doesn't. Is there a > setting or an option that needs to be
turned on? Or some other way of > running a procedure by
supplying the parameters from within excel?
Click to expand...
 
Back
Top