Open store procedure from VBA

[Jose Perdigao]

I created a sp procedure with multi select statement.

From query analyzer, I open any select, depending of iCrint() value

The following code is example:

ALTER PROCEDURE P2_qrys

--@STR INT

AS

SET NOCOUNT ON

DECLARE @STR INT
SET @STR=dbo.iCrint()

--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- Allocation
IF @STR=1

SELECT dDatez, Fieldd, Satellite, SepProdz, PipelineID, WellID, TestDate
FROM dbo.L1_AllocationDF
WHERE (Area LIKE dbo.iArea())
ORDER BY dDatez, Sort

--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- Alloc Factors
IF @STR=5

SELECT dDate, Area, Source, FctOil, FctWater, FctGross, FctGas
FROM dbo.L1_AllocFctrsDF
WHERE (Area LIKE dbo.iArea())
ORDER BY dDate

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- Chemicals
IF @STR=7

SELECT dDate, Area, ChemicalType, InjectionPoint, ChemicalName, Supplier
FROM dbo.L1_ChemicalsDF
WHERE (Area LIKE dbo.iArea())
ORDER BY dDate, Sort

RETURN

From VBA I open store procedure and sometimes works, sometimes not and
sometimes the select statement doesn't matching with the select statement.

If in the query analyzer I can open the right statement what is wrong? What
can I do?

What I need is:

I have many and many queries. I can create one store procedure for each
query, I did and works fine.

But I would like reduce the number of queries, so I think, I can create a sp
with multi SELECTs and with function IF, I would like to open the query that
I wihs.

Any suggestions?

Thanks,
JP

 

[Sylvain Lafontaine]

The most likely explanation for this is that iCrint() doesn't always return
the value that you think it would have (or should have) returned.

A possible explanation would be some concurrency issue: if you store the
value to be returned in a table and then call P2_qrys, it's possible that
the value that you have just written has not been committed yet; hence
iCrint() will be unable to read the latest written value. Same possible
problem with dbo.iArea().

Insert some debug code that will store the value of @STR into a table -
along with the current date/time - or take a look with the SQL-Server
Profiler to make sure that iCrint() works the way you think it should work.

A possible solution would be to pass the value @STR as a parameter to the
stored procedure P2_qrys.

From the amount of explanation that you have given to us, it's impossible to
give you more help.

Finally, your idea is not a very good idea in term of efficiency because it
will be hard for SQL-Server to optimize the query plan for each SELECT
statement. I've also some concerns about the construct « Area LIKE
dbo.iArea()) ». I didn't test it and I don't know if the use of a function
like dbo.iArea() as the argument to the operator LIKE will block the
etablishment of a good optimization plan; however this is something that you
should look into if you don't want your application to become a turtle.

 

[Jose Perdigao]

Thanks Sylvan

The functions dbo.iArea and dbo.iCrint and the command Like works fine in my
database.
I test the sql statements using the command like is a litle bit slower but
is perferctly acceptable and I reduce sql statments.

Abou my question:
USE PUBS

ALTER PROCEDURE P2_qrysX
@STR INT
AS

-- Statement 1
IF @STR=1
SELECT *
FROM dbo.authors

-- Statement 2
--IF @STR=2
SELECT *
FROM dbo.discounts

-- Statement 3
IF @STR=3

SELECT *
FROM dbo.employee

RETURN


If I pass parameter 1, the result is correct, if I pass parameter 2, the
column names are from the satatement 1 and the data are from statement 2

Do you have a solution to works with multi-statement in store procedures or
in functions?
Could yuou give an example using pubs or northwin?

Thanks

JP

 

[Sylvain Lafontaine]

From your post, I understand that you never any problem with this SP when
running it Query Analyser but only when calling it from VBA under Access.

So, how are you calling your procedure in VBA?

 

[Jose Perdigao]

When I select a item from list box I pass the parameter to a table where
iCrint() will get the value.
This is ok, I use this procedure all procedures and it works properly

To open the procedure I use the following code:
DoCmd.OpenStoredProcedure "P2_qrys"

The problem is about columns,
If the iCrint()=1, this is mean for the first SELECT, it works fine and
correct. For the others SELECTS doesn’t work properly. It always shows the
same columns of the first SELECT.

From ADP if I do double click in the sp, the problem is the same.

So, I would like to know, how can I return a specific SELECT from multi-
Select in the store procedure or from functions.

I think with IF is not good away.

Do you have idea?

Regards,
JP

 

[Sylvain Lafontaine]

I'm afraid that DoCmd.OpenStoredProcedure and linked tables/SP/Views offer
support for only one set of metatags and that these metatags are not
refreshed automatically before each call.

If you want to use DoCmd.OpenStoredProcedure, you will have to split your SP
into one for each different select.

If you want to use multiple select with different metatags in the same SP,
then you will have to use the usual collection of ADO objects to make your
call.

 

[Jose Perdigao]

I used DoCmd.OpenStoredProcedure becauset because this is the away tha I know.
One sp for one SELECT this is what I have. But I have many and many sp and I
would like to reduce using multi-select statements.

How can is use collection of ADO objects to make a call the specific SELECT??

Could you give me an example?

Thanks

JP

 

[Sylvain Lafontaine]

For opening the result into a datasheet or not? For opening into a
datasheet, you are out of luck.

If you don't need to display the result, you can simply call the sp from the
Access connection:

Access.CurrentProject.AccessConnection.MyStoredProcedure (first_argument,
.....)

For an example about ADO objects, see:
http://groups.google.ca/group/micro...9f75640?lnk=gst&q=ado&rnum=5#69e953ece9f75640

ADP have been designed to use bound forms with associated SP; if you don't
want to use this development model, then you will find a lot of problems and
maybe you should use something else than ADP.

You can make a direct call to a SP from ADP; however, this functionality is
pretty limited as ADP has not been designed for this.

 

[aaron.kempf]

you could write a wrapper form and it might be a lot easier.

then you could; for examples-- list each of the fields in the
recordsource.. and you could pass in particular parameter values to see
different configurations.

but more realistically; split this into multiple sprocs-- and I would
even reccomend staying away from those types of functions. I don't see
the point; I swear the only thing that it does is slow you down.

-aaron