"Marcin Wasilewski" <ma************ ********@gmail. com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .
Of course you're right.
Below there is code of procedure which a want to call:
CREATE PROCEDURE dbo.[Year to Year Sales]
@BeginningDate DateTime, @EndingDate DateTime
AS
IF @BeginningDate IS NULL OR @EndingDate IS NULL
BEGIN
RAISERROR('NULL values are not allowed', 14, 1)
RETURN
END
SELECT O.ShippedDate,
O.OrderID,
OS.Subtotal,
DATENAME(yy,Shi ppedDate) AS Year
FROM ORDERS O INNER JOIN [Order Subtotals] OS
ON O.OrderID = OS.OrderID
WHERE O.ShippedDate BETWEEN @BeginningDate AND @EndingDate
GO
As you see, I need to give two parametrs and I give back a some data.
Of course I search befor asking, but it is my first time ;) so a realy
don't know, how build this part of code.
So I need call the procedure with 'Year' and 'Year Sales' parametrs.
I've founded somting like that:
objCom.CommandT ype =
objCom.CommandT ext =
objCom.Execute
Thanks for your help.
mw
I'm not sure whether the name of your procedure "Year to Year Sales" matches
what it does. If the idea is to create a recordset based on a single year,
then perhaps you only need one input parameter - the year. You also need to
be careful with "between" when using dates as the order date may also
contain a time portion. I would also avoid putting spaces in the names of
stored procedures as it means you have to use the silly brackets.
I don't know whether I would bother to raise an error in the stored
procedure if you are going to wrap it in a vba function. You could use the
vba coding to check whether you have a start and end date. If you are going
to start raising errors, then perhaps you should think about getting your
stored procedure to provide a return value to show if it was successful.
You then have to get your vba coding to get the value of this output
parameter. I have not done this here, but this gives you the general idea:
Private Sub cmdTest_Click()
On Error GoTo Err_Handler
Dim cnn As ADODB.Connectio n
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rst As ADODB.Recordset
Dim strConn As String
Dim dteStart As Date
Dim dteEnd As Date
dteStart = DateSerial(2006 , 1, 1)
dteEnd = DateSerial(2006 , 2, 1)
strConn = "Provider=sqlol edb;" & _
"Data Source=MyServer ;" & _
"Initial Catalog=MyDatab ase;" & _
"Integrated Security=SSPI"
Set cnn = New ADODB.Connectio n
cnn.Open strConn
Set cmd = New ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandText = "[Year to Year Sales]"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParam eter("@Beginnin gDate", adDate, adParamInput, ,
dteStart)
cmd.Parameters. Append prm
Set prm = cmd.CreateParam eter("@EndingDa te", adDate, adParamInput, ,
dteEnd)
cmd.Parameters. Append prm
Set rst = cmd.Execute
While Not rst.EOF
Debug.Print rst.Fields("Ord erID")
rst.MoveNext
Wend
MsgBox "Done", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
If rst.State <> adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If
Set prm = Nothing
Set cmd = Nothing
If Not cnn Is Nothing Then
If cnn.State <> adStateClosed Then
cnn.Close
End If
Set cnn = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
