Parameter default value

Any ideas how to retrieve a parameter's default value for an Access/Jet
PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?

I created the proc/Query using this SQL:

CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col IS NULL, data_col, argdata_col);

It's Jet 4.0 syntax so you need to put Access XP/2003 into ANSI-92
query mode or use ADO e.g.

CurrentProject.Connection.Execute _
"CREATE PROCEDURE Proc5 (" & _
"argdata_col VARCHAR(10) = 'Hello' " & _
") AS " & _
"SELECT key_col, data_col " & _
"FROM Test " & _
"WHERE data_col = " & _
"IIF(argdata_col IS NULL, data_col, argdata_col); "

I presumed using the respective ADOX.Procedure object's Command object
that it would pre-fill the Parameter object's Value property for me
using the default, but no luck (in the VBE Immediate Window):

Set oCat = CreateObject("ADOX.Catalog")
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Tempo\TestProcs.mdb"
Set oComm = oCat.Procedures("Proc5").Command
? oComm.Parameters("argdata_col").Value = vbEmpty
True

DAO 3.6 doesn't appear to help me either:

Set db = DBEngine.OpenDatabase("C:\Tempo\TestProcs.mdb")
? db.QueryDefs("Proc5").Parameters(0).Value = vbEmpty
True

I hope I'm not faced with parsing the SQL because my next proc/Query
looks like this:

? db.QueryDefs("Proc6").SQL
PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col1 IS NULL, argdata_col2, argdata_col1);

Any ideas? Many thanks.

wrote:
Any ideas how to retrieve a parameter's default value for an Access/Jet
PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?

I created the proc/Query using this SQL:

CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col IS NULL, data_col, argdata_col);

It's Jet 4.0 syntax so you need to put Access XP/2003 into ANSI-92
query mode or use ADO e.g.

CurrentProject.Connection.Execute _
"CREATE PROCEDURE Proc5 (" & _
"argdata_col VARCHAR(10) = 'Hello' " & _
") AS " & _
"SELECT key_col, data_col " & _
"FROM Test " & _
"WHERE data_col = " & _
"IIF(argdata_col IS NULL, data_col, argdata_col); "

I presumed using the respective ADOX.Procedure object's Command object
that it would pre-fill the Parameter object's Value property for me
using the default, but no luck (in the VBE Immediate Window):

Set oCat = CreateObject("ADOX.Catalog")
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Tempo\TestProcs.mdb"
Set oComm = oCat.Procedures("Proc5").Command
? oComm.Parameters("argdata_col").Value = vbEmpty
True

DAO 3.6 doesn't appear to help me either:

Set db = DBEngine.OpenDatabase("C:\Tempo\TestProcs.mdb")
? db.QueryDefs("Proc5").Parameters(0).Value = vbEmpty
True

I hope I'm not faced with parsing the SQL because my next proc/Query
looks like this:

? db.QueryDefs("Proc6").SQL
PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col1 IS NULL, argdata_col2, argdata_col1);

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

Problems: Your 1st "query" is an incorrect T-SQL stored procedure
(incorrect 'cuz you're using IIf() function, which is a VBA function -
not a T-SQL function). The correct stored procedure would look like
this:

CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
CASE WHEN argdata_col IS NULL
THEN data_col
ELSE argdata_col
END

You've explicitly set the default value, "Hello". Why are you trying to
read a known value?

Your final query is neither a stored procedure nor a valid Access query.
Parameters in Access queries do not set the default values in the
PARAMETERS clause. You can't do this:

PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';

Only this:

PARAMETERS
argdata_col1 Text ( 10 ), argdata_col2 Text ( 10 );

You can set a "default" value when the parameter is NULL like this:

WHERE
column = Nz(argdata_col1,";:,:;")
AND column2 = Nz(argdata_col2,"Yes")

I don't know what you're trying to do w/ a default of ';:,'',:;' Do you
mean this:

column In (';:', ':;')

???

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

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

iQA/AwUBQzrQQ4echKqOuFEgEQIzXwCg1L3kDccHl1UIeezS7/e3jJfhmKYAn0IH
UUwOLlVQHc8Ez8FtoDAC19/c
=tAtb
-----END PGP SIGNATURE-----

MGFoster wrote:
Any ideas how to retrieve a parameter's default value for an Access/Jet
PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?

Problems: Your 1st "query" is an incorrect T-SQL stored procedure

Sorry, I thought I'd made it clear this is an Access/Jet PROCEDURE. I
gave the (I hope) Access-friendly name 'parameter Query'. I posted code
to create the proc but perhaps this wasn't explicitly MS Access enough
(could potentially apply to an Access ADP MSDE/MSSQL project perhaps,
hence your T-SQL assumption?)

You've explicitly set the default value, "Hello". Why are you trying to
read a known value?

The data is meaningless. It's just a test

Your final query is neither a stored procedure nor a valid Access query.

What do you mean by 'valid Access query' here? This could just be
semantics i.e. is what results from a Jet CREATE PROCEDURE statement a
'stored procedure'?

Here is some more explicit code to create an .mdb Access/Jet database
with a test table and data, create the proc, re-read the definition
and, to demonstrate it is a valid Jet PROCEDURE, execute it:

Sub Test_Access_Jet()

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 = .OpenSchema(16)
MsgBox rs!PROCEDURE_DEFINITION

Set rs = .Execute("EXECUTE TestProc")
MsgBox rs.Source & vbCr & vbCr & rs.GetString

Set rs = .Execute("EXECUTE TestProc NULL")
MsgBox rs.Source & vbCr & vbCr & rs.GetString

Set rs = .Execute("EXECUTE TestProc 'Plenty'")
MsgBox rs.Source & vbCr & vbCr & rs.GetString

End With

End With
End Sub

I've also opened Test.mdb in Access95 (EngineType=4 creates a Jet 3.x
version .mdb) and TestProc opens OK, i.e. prompts for the parameter
then returns the correct data (but does not apply the default), so I
hope that satisfies the test for 'valid Access query'. (BTW for 'Query
design mode' it open in GUI mode by default, not SQL, and in converting
to SQL it drops the parameter's default value).

I hope this helps you to help me get at this parameter default value.
Thanks again.

MGFoster wrote:
I don't know what you're trying to do w/ a default of ';:,'',:;'

Again, the data is meaningless. My point is, it would be tricky to
parse the SQL to extract the parameter's value because the parameter
value, held in single quotes in the definition, could itself contain
single quotes (the two quotes represents a one escaped quote), commas
(the delimiting character for parameter definitions), semicolons (the
delimiting character between PARAMTERS declaration and main SQL
definition), equals signs (the delimiting character between the main
parameter definition and the parameter default value definition) etc
etc.

MGFoster wrote:
CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
CASE WHEN argdata_col IS NULL
THEN data_col
ELSE argdata_col
END

Now that I think about it, your parameter name is illeagal. Also,
COALESCE is elegant he

CREATE PROCEDURE Proc5 (
@data_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col = COALESCE(@data_col, data_col)

Back in Access/Jet, any ideas about getting that default parameter
value?

Hi,





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,


Vanderghast, Access MVP

wrote in message
oups.com...

MGFoster wrote:
CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
CASE WHEN argdata_col IS NULL
THEN data_col
ELSE argdata_col
END

Now that I think about it, your parameter name is illeagal. Also,
COALESCE is elegant he

CREATE PROCEDURE Proc5 (
@data_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col = COALESCE(@data_col, data_col)

Back in Access/Jet, any ideas about getting that default parameter
value?

wrote:
MGFoster wrote:

CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
CASE WHEN argdata_col IS NULL
THEN data_col
ELSE argdata_col
END


Now that I think about it, your parameter name is illeagal. Also,
COALESCE is elegant he

CREATE PROCEDURE Proc5 (
@data_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col = COALESCE(@data_col, data_col)

Back in Access/Jet, any ideas about getting that default parameter
value?


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

You're correct about the syntax for my parameter in the procedure, I
just forgot the @ at the beginning (I was just blindly following your
example); and, yes, COALESCE() does work well in that situation.

What I was trying to get at about Access vs. T-SQL queries/stored
procedures (SPs). When using an .adp file you are actually using the MS
SQL Server (or, the desktop edition MSDE) DB engine. Your post example
was showing a T-SQL SP (not an Access query) which was using the VBA
function IIf(); this is incorrect in T-SQL.

Concerning retrieving the default value of a SP: There isn't any easy
way to do this. The only way I can think of is to parse the SP's source
by using the following to retrieve the SP text:

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyStoredProcedureName'

The ROUTINE_DEFINITION column holds 4,000 characters of the SP
definition. You'd have to parse out your parameters' defaults from
that.

What I was getting at when I stated "why are you trying to read a known
value" was - for someone writing a query that uses a SP, that person
should know the defaults of the SP parameters; either thru documentation
or 'cuz that person wrote the SP. ;-) If you're doing it on the fly,
then something very strange, and, therfore, probably very wrong is going
on.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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

iQA/AwUBQzwVWYechKqOuFEgEQK5BgCfSQkfTRpNVXIbp4bdH+Cvmk l+R5kAnRk5
PwW+IaCIESWc9qUY/m/fai47
=IUuH
-----END PGP SIGNATURE-----

MGFoster wrote:
What I was trying to get at about Access vs. T-SQL queries/stored
procedures (SPs). When using an .adp file you are actually using the MS
SQL Server (or, the desktop edition MSDE) DB engine. Your post example
was showing a T-SQL SP (not an Access query) which was using the VBA
function IIf(); this is incorrect in T-SQL.

Please take another at my code. This is not T-SQL, not an .adp file,
not SQL Server, not MSDE. It really is Jet/Access. Honest. Please try
running my VBA code: it creates a Access/Jet database, table, data,
PROC/Query and uses it.

wrote:
MGFoster wrote:

What I was trying to get at about Access vs. T-SQL queries/stored
procedures (SPs). When using an .adp file you are actually using the MS
SQL Server (or, the desktop edition MSDE) DB engine. Your post example
was showing a T-SQL SP (not an Access query) which was using the VBA
function IIf(); this is incorrect in T-SQL.


Please take another at my code. This is not T-SQL, not an .adp file,
not SQL Server, not MSDE. It really is Jet/Access. Honest. Please try
running my VBA code: it creates a Access/Jet database, table, data,
PROC/Query and uses it.


I suggest you read the documentation.

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

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.

Thanks again.