Error 3708 on Stored Procedure with a parameter 1

[git2dn]

I created a dynamic query in my Access application as a SQL string. The user selects values from various controls on the screen including list boxes and based on the values selected from the varous controls, I built a SQL string.

Now I want to pass along the SQL string named strSQL to a Stored Procedure using a parameter.

I coded the following in my Access application to execute a stored procedure named dbo.procUDFl with a parm named prmSQL, which contains a complete SQL statement.

Let's assume the value for parameter prmSQL is as follows:

SELECT * FROM tblStates WHERE STATE = 'NY';

I tried to execute the Stored Procedure dbo.procUDLl with parameter prmSQL.

However, I get an error 3708 on the Append statement which follows:

----------------------------------
I coded the following:

Option Explicit
Dim prmSQL As ADODB.Parameter
...

strSQLFS = "Select * from dbo.tblCustomers;"

Set com = New ADODB.Command
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDFl"
.Parameters.Refresh
.Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput, , strSQLFS)
.ActiveConnection = cn
Set rstQueryFS = .Execute
End With
-----------------------------------------------------------
I coded the stored procedure as follows:

CREATE PROCEDURE dbo.procUDFl

@prmSQL varchar (4000)

AS

EXEC(@prmSQL)
GO

 

[cmmrfrds]

The adVarChar needs a length for one thing.

Parameters.Append .CreateParameter("@prmSQL", adVarChar, adParamInput,4000 , strSQLFS)

 

[git2dn]

cmmrfrds, your observation was exactly what I needed

thanks a million.