OpenStoredProcedure and Parameters

[Eric]

I am converting and existing MDB file to a MDB project. Most of the work is
complete. I have run into a problem. I need to execute a stored procedure
and display the results in a dataview. The function OpenStoredProcedure does
exactly that, except for one minor problem. It doesn't support parameters.

So what to do? I have created a new table called AdHocQueries that contains
the user's username, the stored proc to run, and room for two arguments; a
new stored procedure that inserts records into the table; and a stored
procedure that deletes the record. So now the code looks like this:

AddAdHocQuery( UserName, StoredProc, Param1, Param2)
OpenStoredProcedure "AdHocQuery"
DeleteAdHocQuery( UserName )

For testing purposes the stored proc AdHocQuery has the code:

SET NOCOUNT ON;
EXEC MyStoredProc 123, 'ABC'

This works like a charm. The dataview is opened just like you would expect.
So now I modify the stored proc AdHocQuery to read from the table and this
is the code.

SET NOCOUNT ON

DECLARE @UserName varchar(50)
DECLARE @JobName varchar(50)
DECLARE @Param1 varchar(50)
DECLARE @Param2 varchar(50)
DECLARE @cmd varchar(8000)

SELECT @UserName = user
SET @UserName = substring(@UserName,charindex('\',@UserName)+1,99)

SELECT @JobName=JobName, @Param1=Param1, @Param2=Param2 FROM AdHoc WHERE
UserName=@UserName

SET @cmd = @JobName + '''' + @Param1 + ''', ''' + @Param2 + ''''

EXEC( @cmd )

Now whenever the OpenStoredProcedure statement is executed I get the error
message "The stored procedure executed successfully but did not return any
records."

Basically I need the dataview to show the recordset returned by executing
another stored procedure. It appears that having the first two select
statements in the stored proc is confusing the crap out of Access.

Any ideas?

 

[Tom van Stiphout]

On Tue, 15 Apr 2008 14:01:01 -0700, Eric

You are using confusing language (to me).
There is no such thing as an "MDB project"; I take it you mean "ADP
project".
What do you mean by "dataview"? Do you mean a form in datasheet view?

OpenStoredProcedure is a toy method not suitable for serious work..

Room for two arguments? Doesn't that fail for the same reason as "room
for zero arguments"? If you take the time anyway, design something
that works for all situations. Did you consider OUTPUT parameters,
and the RETURN option?

To come back to your initial question (with some assumptions): you are
in an ADP and create a form in Datasheet view and set the
Form.RecordSource property to your sproc name, and the
Form.InputParameters according to your sproc's requirements (see help
file for details).
Then just docmd.openform your form and voila the data will be showing.

-Tom.

 

[Eric]

I fixed it by changing:

EXEC( @cmd )

to

EXECUTE sp_executesql @cmd;