OpenStoredProcedure and Parameters

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?
.

Relevant Pages

  • Re: Using select with passed table name
    ... EXECUTE sp_executesql @SQL_String, ... In your case, you don't use that value in the stored procedure, but you could. ... DECLARE @Parameter_Definition NVARCHAR ... declare @ktr smallint ...
    (microsoft.public.sqlserver.programming)
  • Re: need some advise
    ... I would opt for creating a stored procedure and executing that. ... > need some advise on the best way to do something, i have written a script ... > that i want to execute from an application that i am writting using ... > declare @name varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • Re: Checking for null
    ... >> email address to recover a forgotten username. ... After I execute the stored procedure I try ... >> is the correct way to check for nulls in ASP.NET? ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: UniqueIdentifier column
    ... >> I am trying to execute the following stored procedure by the ... > The value you are using is not a valid uniqueidentifier. ... > DECLARE @LockID UNIQUEIDENTIFIER ...
    (microsoft.public.sqlserver.clients)
Loading