When parameter value is wider than parameter's defined width

[Jamie Collins]

Have you ever noticed what happens when you pass a value of
NVARCHAR(x) to a Jet SQL PROCEDURE parameter defined as NVARCHAR(y)
where x > y?

For example:

CREATE PROCEDURE TestProc
(arg_text VARCHAR(10))
AS SELECT LEN(arg_text);
;
EXECUTE TestProc SPACE(255)
;

[Translation for traditional Access speakers: Have you ever noticed
what happens when you pass a value of TEXT ( x ) to a parameter in a
Parameter Query defined as TEXT ( y ) where x > y?]

Spoiler below; meanwhile, here's some VBA to reproduce the behavior:

Sub ParamVarchar()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
' Parameter defined as width=10
.Execute _
"CREATE PROCEDURE TestProc (arg_text" & _
" VARCHAR(10)) AS SELECT LEN(arg_text);"
Dim rs
' Parameter value passed width=255
Set rs = .Execute( _
"EXECUTE TestProc SPACE(255);")
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

The message returns, 255. The defined width of the VARCHAR(10)
parameter has no effect on the value passed. I can't believe I've only
noticed this today <g>!

BTW I don't recommend you try this but you get some nasty errors when
you attempt to pass a parameter value wider than 255 characters; the
effects/messages vary but sometime it's literally "Catastrophic
failure"

Jamie.

--