Pass parameter to sp

[Jose Perdigao]

I have the follwing sp

ALTER PROCEDURE P2_DataForms

@STR INT

AS

--DECLARE @STR INT

--SET NOCOUNT ON

--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

IF @STR=1

BEGIN

IF EXISTS (SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'ZP2_DataForms')
DROP TABLE ZP2_DataForms

SELECT dDate, Wellz, Fieldd, Satellite, TestDate, Status, Choke,
TestAPI, DownTime, TOil, ActualOil, AllocOil, LostOil, TWat, ActualWat,
LostWat, TGas,
ActualGas, AllocGas, LostGas, SepProd, PipelineID
INTO dbo.X1
FROM dbo.J1_AllocationDF
WHERE (Area LIKE dbo.iArea())
ORDER BY dDate, Sort

END

--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

IF @STR=5

BEGIN

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

END
--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

IF @STR=16

BEGIN

SELECT dDate, Block, Area, Facility, Source, Name, Tmp, Ps, Pd, Gas
FROM dbo.J1_GasProdDF
WHERE (Area LIKE dbo.iArea())
ORDER BY dDate, Sort

END

RETURN

From vba I need generate recordset depending the parameter, as the following:

Public Function testingX()
Dim cn As adodb.Connection
Dim rc As adodb.recordset

Set cn = CurrentProject.Connection
Set rc = New adodb.recordset

'I need to call the statement @STR=5 and using the following command:
rc.Open "P2_Dataforms", cn

'How can Ipass the parameter?

End Function

'-------------------------------
Question 1
How can I pass the parameter to generate recordset for @STR=5? Using
(rc.Open "P2_Dataforms", cn)

Question 2
It is important o add BEGIN and END for each IF? It works without BEGIN/END

Any Help?

Thanks a lot,
jcp

 

[PBsoft]

I have the follwing sp

ALTER PROCEDURE P2_DataForms [omissis]
From vba I need generate recordset depending the parameter, as the
following:

Public Function testingX()
Dim cn As adodb.Connection
Dim rc As adodb.recordset
Set cn = CurrentProject.Connection
Set rc = New adodb.recordset
'I need to call the statement @STR=5 and using the following command:
rc.Open "P2_Dataforms", cn

'How can Ipass the parameter?

End Function

'-------------------------------
Question 1
How can I pass the parameter to generate recordset for @STR=5? Using
(rc.Open "P2_Dataforms", cn)

To use a parameterized stored procedure from VBA you must use an ADO Command,
which can have parameters.
Then you must assign the Execute-method results to an ADO Recordset.

Question 2
It is important o add BEGIN and END for each IF? It works without
BEGIN/END

"Begin..End" in T-SQL are like parenthesis in maths: you can abuse