Default query parameter values

[The Plankmeister]

Hi...

I have a query which I'm accessing through PHP as a stored procedure.
However, I need to be able not to pass a couple of parameters in certain
situations. When I do this, I get an error:

[Microsoft][ODBC Microsoft Access Driver] Parameter [@str_year] has no
default value.

But it works fine in Access when I run the query from there and just hit
enter on the relevant parameter dialog. Is there a way of setting a default
value for a query parameter? I can't find out anything about it...

TIA.
Plankmeister.

[MacDermott]

AFAIK Access has no way to set/store default parameter values.
When you "just hit enter", you are entering the empty string which was
displayed in the parameter dialog.
You might try setting that parameter to a blank string programatically .

HTH
- Turtle

"The Plankmeister" <pl************ ******@hotmail. com> wrote in message
news:3f******** *************** @dread12.news.t ele.dk...

Hi...

I have a query which I'm accessing through PHP as a stored procedure.
However, I need to be able not to pass a couple of parameters in certain
situations. When I do this, I get an error:

[Microsoft][ODBC Microsoft Access Driver] Parameter [@str_year] has no
default value.

But it works fine in Access when I run the query from there and just hit
enter on the relevant parameter dialog. Is there a way of setting a default value for a query parameter? I can't find out anything about it...

TIA.
Plankmeister.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm assuming you mean that you have a stored procedure (SP) in MSDE or
MS SQL Server, not a SP in PHP. Here is how to define a default
parameter value in a MS SQL Server SP:

CREATE PROCEDURE usp_thisSP
@first_paramete r VARCHAR(32) = 'business'
@second_paramet er MONEY = 200.00
AS
.....

I believe you can use a method described in KB103181 (my modification
for MS SQL Server - untested - don't know if setting a variable to a
NULL will work):

CREATE PROCEDURE usp_AnotherExam ple
@ShipCountry VARCHAR(4) = NULL
AS
.....

WHERE @ShipCountry IS NULL
OR (Orders.ShipCou ntry=@ShipCount ry AND @ShipCountry IS NOT NULL )

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9TzzIechKq OuFEgEQL/ggCg9aXgPgKZWKd IsRNnsuOM5lisb0 cAoPyh
PK4Y6I9/36a+Uoq2FlH3KUo M
=8YF3
-----END PGP SIGNATURE-----
The Plankmeister wrote:

Hi...

I have a query which I'm accessing through PHP as a stored procedure.
However, I need to be able not to pass a couple of parameters in certain
situations. When I do this, I get an error:

[Microsoft][ODBC Microsoft Access Driver] Parameter [@str_year] has no
default value.

But it works fine in Access when I run the query from there and just hit
enter on the relevant parameter dialog. Is there a way of setting a default
value for a query parameter? I can't find out anything about it...

TIA.
Plankmeister.