Parameter default value

Michel Walsh wrote:
iif( myParam Is Null, default_value, myParam )

assuming that if the parameter has a Null value, it is to be considered that
the parameter is not supplied,

I'm not sure I understand you point.

In my example (upthread) the call

EXECUTE TestProc

i.e. *omitting* the parameter returns a different resultset to the call

EXECUTE TestProc NULL

i.e. *explicitly* setting the parameter value to NULL. The behaviour
is, when the parameter is omitted the default value bites. This
demonstrates that Access/Jet will use the default value (unless it has
been explicitly, set even to null).

What I need to be able to do is the extract that default value from the
schema. You seem to be suggesting that by running the PROC/Query
without the parameter I will be able to see the effect that the omitted
parameter has had and therefore be able to ascertain what the value is.

I can't see how this could work. For one, the parameter value may not
be revealed at all e.g.

CREATE PROC TestProc2 (arg1 INTEGER = 99) AS
SELECT * FROM Test
WHERE key_col BETWEEN 1 AND arg1

For another, it may be a SQL command e.g.

CREATE PROC TestProc3 (arg1 INTEGER = 1) AS
DELETE FROM Test
WHERE key_col = arg1

Any further thoughts? Thanks again.

Hi,


EXECUTE TestProc


won't run, you will get an error, if some arguement is defined in the
argument list.

If you use a parameter without declaring it:

===============
CurrentProject.Connection.Execute "CREATE PROC jojova(x Integer ) AS SELECT
iota FROM iotas WHERE iota =param"

? CurrentProject.Connection.Execute("EXECUTE jojova").Fields(0).Value
-- error about Too few parameters, 2 expected.

? CurrentProject.Connection.Execute("EXECUTE jojova 5 ").Fields(0).Value
-- error about not having a DEFAULT value

CurrentProject.Connection.Execute "DROP PROC jojova"
=============

you get a different error, from ADO, about the parameter param not having a
DEFAULT value. It seems that Jet reports the error differently to ADO.


So it sounds that the concept is considered, but may be not fully
implemented. I try to get more details, and if I find any, I will post them
here.



Hoping it may help,
Vanderghast, Access MVP


wrote in message
oups.com...

Michel Walsh wrote:
iif( myParam Is Null, default_value, myParam )

assuming that if the parameter has a Null value, it is to be considered
that
the parameter is not supplied,

I'm not sure I understand you point.

In my example (upthread) the call

EXECUTE TestProc

i.e. *omitting* the parameter returns a different resultset to the call

EXECUTE TestProc NULL

i.e. *explicitly* setting the parameter value to NULL. The behaviour
is, when the parameter is omitted the default value bites. This
demonstrates that Access/Jet will use the default value (unless it has
been explicitly, set even to null).

What I need to be able to do is the extract that default value from the
schema. You seem to be suggesting that by running the PROC/Query
without the parameter I will be able to see the effect that the omitted
parameter has had and therefore be able to ascertain what the value is.

I can't see how this could work. For one, the parameter value may not
be revealed at all e.g.

CREATE PROC TestProc2 (arg1 INTEGER = 99) AS
SELECT * FROM Test
WHERE key_col BETWEEN 1 AND arg1

For another, it may be a SQL command e.g.

CREATE PROC TestProc3 (arg1 INTEGER = 1) AS
DELETE FROM Test
WHERE key_col = arg1

Any further thoughts? Thanks again.

Michel Walsh wrote:

EXECUTE TestProc

won't run, you will get an error, if some arguement is defined in the
argument list.

It runs for me without error. Did you try running my code to create the
db, proc, etc?

If you use a parameter without declaring it
you get a different error, from ADO, about the parameter param not having a
DEFAULT value.

Did you try it with a param that *does* exist? Suggested example for
you:

CurrentProject.Connection.Execute "CREATE PROC jojova(x INTEGER = 1) AS
SELECT
iota FROM iotas WHERE iota = x"

? CurrentProject.Connection.Execute("EXECUTE jojova").Fields(0).Value

This should execute OK and render as WHERE iota = 1 because the
parameter default value will bite. Are you seeing this?

Hi,


It was not working, initially, for some reason, but now, with you, that
was making two people reporting it was working.... SO.... I tried it over,
on a new database, and indeed, it works. I don't know what went wrong when I
initially did try it. :-(


Vanderghast, Access MVP



wrote in message
oups.com...

Michel Walsh wrote:

EXECUTE TestProc

won't run, you will get an error, if some arguement is defined in the
argument list.

It runs for me without error. Did you try running my code to create the
db, proc, etc?

If you use a parameter without declaring it
you get a different error, from ADO, about the parameter param not having
a
DEFAULT value.

Did you try it with a param that *does* exist? Suggested example for
you:

CurrentProject.Connection.Execute "CREATE PROC jojova(x INTEGER = 1) AS
SELECT
iota FROM iotas WHERE iota = x"

? CurrentProject.Connection.Execute("EXECUTE jojova").Fields(0).Value

This should execute OK and render as WHERE iota = 1 because the
parameter default value will bite. Are you seeing this?

wrote:
MGFoster wrote:

I suggest you read the documentation.


Thank you. Which documentation? The Microsoft Jet SQL Reference for
Data Definition Language:

http://office.microsoft.com/en-us/as...322191033.aspx

doesn't even mention the use of default values.

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

In the Access Help hierarchy:

Microsoft Jet SQL Reference
Data Definition Language
CREATE PROCEDURE Statement - look at the example link
(at the bottom of the page)

The example is in the Access Help file not on the Microsoft page you
cited above.

Note that in Jet a default can't be assigned to the parameters in the
parameters declaration line.

In reality the Jet CREATE PROCEDURE statement is the same as a Jet query
w/ a PARAMETERS line. E.g.:

A Jet "procedure":

CREATE PROCEDURE this Date, that Text(2);
SELECT * FROM table_name WHERE col1 = this AND col2 = that;

A Jet select query:

PARAMETERS this Date, that Text(2);
SELECT * FROM table_name WHERE col1 = this AND col2 = that;

See? The same, except for the 1st line.

Then read the MS SQL Server Books on Line (the SQL Server Help file).
Compare the CREATE PROCEDURE statement to the Jet CREATE PROCEDURE
statement. A default can be assigned to the SQL Server (T-SQL)
parameters. As I've been saying in previous posts you are mixing the 2
together. Learn the proper syntax for each version.

Good luck.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQz1zoIechKqOuFEgEQIetwCfcz84QsGw7HYLFoLIBJo69T YhNtoAnibQ
e15C1HhsDEocTFoCy25Fn5Rp
=k+9c
-----END PGP SIGNATURE-----

MGFoster wrote:
Note that in Jet a default can't be assigned to the parameters in the
parameters declaration line.

Where does it say this in the help file or documentation? If my link is
wrong (and it seem to be correct to me) then can you please supply the
correct link or a direct quote from the documentation that I can google
for it (I only have Access95 installed at the moment, which I need, and
if I install Access2003 it will automatically uninstall Access95, so
I'm relying on online resources at the moment).

The truth is, in Jet a default *can* be assigned in the parameters
declaration. My example code demonstrates this. Please try it. I'll
post it again here, slightly modified:

Sub Test_Access_Jet3()
On Error Resume Next
Kill "C:\Test.mdb"
On Error GoTo 0

Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=4;" & _
"Data Source=C:\Test.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
" key_col INTEGER NOT NULL," & _
" data_col VARCHAR(10));"
.Execute _
"INSERT INTO Test VALUES" & _
" (1, 'Plenty');"
.Execute _
"INSERT INTO Test VALUES" & _
" (2, NULL);"
.Execute _
"INSERT INTO Test VALUES" & _
" (3, 'N/A');"
.Execute _
"CREATE PROCEDURE TestProc " & _
"(arg_data_col VARCHAR(10) = 'N/A') AS " & _
" SELECT Key_col, data_col" & _
" FROM Test" & _
" WHERE data_col = " & _
" IIF(arg_data_col IS NULL," & _
" data_col, arg_data_col);"

Dim rs As Object
Set rs = .Execute("EXECUTE TestProc")
MsgBox rs.Source & vbCr & vbCr & rs.GetString
End With

End With
End Sub

Note that in the parameters declaration I have assigned the default as
'N/A' i.e.

arg_data_col VARCHAR(10) = 'N/A'

I've then executed the proc and omitted an explicit parameter value
from the call:

EXECUTE TestProc

One row is returned, being the row where data_col = 'N/A'.

My conclusion is that, in absence of an explicit value, Jet has
recognized, honored and applied the default value. How else would you
explain the fact that just one row is returned and it is the row where
data_col = 'N/A'?

As I've been saying in previous posts you are mixing the 2
together. Learn the proper syntax for each version.

And as I keep replying, this has nothing to do with SQL Server and I am
not mixing my syntax. If you could test my code and post back your
conclusions I would be extremely grateful. Many thanks for your help.

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

You've created some hybrid ADO/DAO query in Access. After you create
your Test.mdb open it & look at the query you've created. It looks like
an Access DAO QueryDef, but acts like a Stored Procedure - the default
gets set, which I can't find any documentation on why it does that.

The following is the result of your CREATE PROCEDURE statement - a
typical Access query.

PARAMETERS arg_data_col Text ( 255 );
SELECT Test.key_col, Test.data_col
FROM Test
WHERE (((Test.data_col)=IIf([arg_data_col] Is
Null,[data_col],[arg_data_col])));

One that I had posted in one of my earlier posts.

The WHERE clause is equivalent to this:

WHERE data_col = Nz(arg_data_col, data_col)

Which means if the parameter is NULL, then compare the data_col to the
data_col (a True evaluation), which will return all rows.

BUT, your WHERE clause doesn't work like the Nz() function WHERE clause.
It appears to fill the parameter w/ the default value "N/A"; therefore,
something "underneath" is going on that I don't understand. Apparently,
by using ADO, you've created some hidden Default value for the parameter
-- in Access 95 format, no less! Good grief....

Back to your original Q: How to read the default value? Since it
doesn't show in the SQL view AND it appears to be undocumented, I
haven't the slightest....
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQ0Hf4IechKqOuFEgEQIIdQCdEf+KQfn5Ewnkc6n9085Eq5 9l6+EAoKEl
GW+BQqsyQEZpwZIB6tw5Ns/q
=MEmI
-----END PGP SIGNATURE-----
wrote:
MGFoster wrote:

Note that in Jet a default can't be assigned to the parameters in the
parameters declaration line.


Where does it say this in the help file or documentation? If my link is
wrong (and it seem to be correct to me) then can you please supply the
correct link or a direct quote from the documentation that I can google
for it (I only have Access95 installed at the moment, which I need, and
if I install Access2003 it will automatically uninstall Access95, so
I'm relying on online resources at the moment).

The truth is, in Jet a default *can* be assigned in the parameters
declaration. My example code demonstrates this. Please try it. I'll
post it again here, slightly modified:

Sub Test_Access_Jet3()
On Error Resume Next
Kill "C:\Test.mdb"
On Error GoTo 0

Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=4;" & _
"Data Source=C:\Test.mdb"
With .ActiveConnection
.Execute _
"CREATE TABLE Test (" & _
" key_col INTEGER NOT NULL," & _
" data_col VARCHAR(10));"
.Execute _
"INSERT INTO Test VALUES" & _
" (1, 'Plenty');"
.Execute _
"INSERT INTO Test VALUES" & _
" (2, NULL);"
.Execute _
"INSERT INTO Test VALUES" & _
" (3, 'N/A');"
.Execute _
"CREATE PROCEDURE TestProc " & _
"(arg_data_col VARCHAR(10) = 'N/A') AS " & _
" SELECT Key_col, data_col" & _
" FROM Test" & _
" WHERE data_col = " & _
" IIF(arg_data_col IS NULL," & _
" data_col, arg_data_col);"

Dim rs As Object
Set rs = .Execute("EXECUTE TestProc")
MsgBox rs.Source & vbCr & vbCr & rs.GetString
End With

End With
End Sub

Note that in the parameters declaration I have assigned the default as
'N/A' i.e.

arg_data_col VARCHAR(10) = 'N/A'

I've then executed the proc and omitted an explicit parameter value
from the call:

EXECUTE TestProc

One row is returned, being the row where data_col = 'N/A'.

My conclusion is that, in absence of an explicit value, Jet has
recognized, honored and applied the default value. How else would you
explain the fact that just one row is returned and it is the row where
data_col = 'N/A'?


As I've been saying in previous posts you are mixing the 2
together. Learn the proper syntax for each version.


And as I keep replying, this has nothing to do with SQL Server and I am
not mixing my syntax. If you could test my code and post back your
conclusions I would be extremely grateful. Many thanks for your help.

MGFoster wrote:
something "underneath" is going on that I don't understand. Apparently,
by using ADO, you've created some hidden Default value for the parameter
-- in Access 95 format, no less! Good grief....

Back to your original Q: How to read the default value? Since it
doesn't show in the SQL view AND it appears to be undocumented, I
haven't the slightest....

Oh well. Thanks for sticking with me, though.