Running SQL Server stored procedure from Excel macro

[wfweirich]

I have a stored procedure that I would like to run within an Excel macro. The usp_ has input and output parms. I currently have multiple examples of running usp_ to bring back record sets to .Net apps and Excel. I am struggling with the code needed to establish parms going in, then using the output parms, once populated, to present just that value(s) on a spreadsheet. Thanks in advance for your help. wfw

 

[NoCoolHandle]

Ok... Assuming you are using EXCEL and have ado installed...

1. in the code environment "Reference" Microsoft ADO

2. In you code you need
a. A connection
and depending on if your stored proc returns records or has output or return parameters you might need a command object and or a recordset object..
and that that recordset has only 5 fields (columns) and the stored proc has 4 params
Assuming Recordset (the norm) - the following code should be functional.. (given the right parameters) - note single quotes around strings... - code not tested. just created here for the demo but should work.

Sub SOmeProc
on error goto errhand
Dim c as adodb.connection
dim r as adodb.recordset
set c = new adodb.connection
set r = new adodb.recordset
c.open ("provider=sqloledb;server=yourserver;database=yourdatabase;integrated security=sspi") 
'Opens a trusted(NT login) connection to your sql box
r.open "StoredPRocName 'textparam',77,true,'2006/12/23", c
dim x as integer
x=1
while not r.eof
cells(X,1)=r(x-1).fields(0).value ' the first field
cells(X,2)=r(x-1).fields(1).value ' the Second field
cells(X,3)=r(x-1).fields(2).value ' the Third field
cells(X,4)=r(x-1).fields(3).value ' the Fourth field
cells(X,5)=r(x-1).fields(4).value ' the Fifth field
 x= x+1
r.movenext
loop
r.close
c.close
set r = nothing
set c = nothing
exit sub
ErrHand:
  msgbox err.description
end sub

hth


Rob

 

[wfweirich]

This is good stuff, and I will hang on to it. But, I need to see how output parms are handled, not a record set. Thanks. wfw

 

[NoCoolHandle]

Ok output params need a command object. you also need to ensure that the command object specifies a type of storedproc other wise the values never come back..

Assuming a proc

Create Proc ABC @x int, @y int output

where @x is and input param and @y is an output param..

Function USEABC(someparam as integer) as integer
on error goto errhand
Dim c as adodb.connection
dim cm as adodb.Command
set c = new adodb.connection
set cm = new adodb.Command
c.open "provider=sqloledb;server=yourserver;database=yourdatabase;integrated security=sspi"
'Opens a trusted(NT login) connection to your sql box
with cm
 .commandtype=adcmdstoredproc
 .commandtext = "abc"
 .parameters.append .parmeters.create("@x",adinteger,adparaminput)
 .parameters.append .parmeters.create("@x",adinteger,adparamoutput)
.activeconnection =c
.parameters("@x").value=someparam
.execute
USEABC = .parameters("@y").value
.activeconnection=nothing
end with


c.close
set cmd = nothing
set c = nothing
exit function
ErrHand:
  msgbox err.description
end function

Then calling it

cells(1,3)=useabc(243)

should return the output param to cell 1,3 of the active workbook,

HTH


Rob