Executing a parameterized stored procedure from Excel

Question

Executing a parameterized stored procedure from Excel

pietlinden

SSC Guru

Points: 63503
More actions
August 12, 2013 at 1:57 pm

#278588

I'm using Excel 2010 (if it matters), and I can execute a stored procedure fine - as long as it has no parameters.
Create a new connection to SQL Server,
then in the Connection Properties dialog, specify
Command Type: SQL
Command Text: "SCRIDB"."dbo"."uspDeliveryInfo"
but if I want to pass a parameter, I would normally do something like
SCRIDB.dbo.uspDeliveryInfo @StartDate = '1/1/2010', @EndDate = GETDATE()
but in this case, I would want to pass the values from a couple of cells in the worksheet. Do I have to use ADO (so this isn't a SQL Server question at all?)
Sorry, searched around all over here, and couldn't find an answer, so I thought I'd ask.
Thanks!
Pieter
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Chrissy321 SSCoach Points: 15763 More actions · Answer 1

I haven't thought about or looked at this code in many years....but maybe it will help you. The parameters are in cells B2 and D2.

Private Sub CommandButton1_Click()

Dim cn As ADODB.Connection

Dim cmd As ADODB.Command

Dim rs As ADODB.Recordset

Dim strConn As String

Set cn = New ADODB.Connection

strConn = "PROVIDER=SQLOLEDB;"

strConn = strConn & "SERVER=Server;INITIAL CATALOG=Database;"

strConn = strConn & " INTEGRATED SECURITY=sspi;"

cn.Open strConn

Set cmd = New ADODB.Command

cmd.ActiveConnection = cn

cmd.CommandText = "yourproc"

cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh

cmd.Parameters(1).Value = Range("B2")

cmd.Parameters(2).Value = Range("D2")

Set rs = cmd.Execute()

If Not rs.EOF Then

Worksheets("sheet1").Range("A5:F500").CopyFromRecordset rs

rs.Close

Else

MsgBox "No data."

End If

pietlinden SSC Guru Points: 63503 More actions · Answer 2

I was afraid I was going to have to use that. No way to pass a parameter directly to a stored procedure through the GUI. No big surprise there.

Thanks!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

pietlinden (8/12/2013)
I was afraid I was going to have to use that. No way to pass a parameter directly to a stored procedure through the GUI. No big surprise there.
Thanks!

Yes, you can do what you want.

You need to select MSQuery as the data source, then in MSQuery - click the button to edit the SQL statement directly.

Enter your SQL like this:

{call dbo.my_proc (?,?)}

...using one ? for each parameter.

MSQuery will complain it cannot display the query in the designer - this is fine, just OK it, then enter some suitable parameter values.

Once you have some data, select "Return data to excel" (or similar) from the file menu and then you will be able to click the "Parameters" button to edit the Query parameters and tell excel to take them from cells on the spreadsheet.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

pietlinden SSC Guru Points: 63503 More actions · Answer 4

MM,

super cool... got it to work... now to sort out the parameter stuff!

Pieter

kirkevans Valued Member Points: 59 More actions · Answer 5

Thank you!

This is just what I need, with one small problem - I can't find the "Parameters" button that you mention.

pietlinden SSC Guru Points: 63503 More actions · Answer 6

One option is to modify the Connection properties that are being used to execute the stored procedure.

I'm assuming you're using Excel 2013... if not, I don't know - haven't used 2010 in a long time.

Data tab ==> Connections

Click the Properties button

Click the Definition tab

Connection type: SQL

Command Text: EXEC dbo.EnrolleeSymptoms 'LUN90';

EXEC schema.StoredProcedureName param1Value param2Value param3Value....

That's probably the easiest way. If you want it to be more flexible, you'd have could change the properties of the connection programmatically. I have it here somewhere in a book, if you need it. Seems like it should be much less of a PITA to pass param values from Excel to a stored procedure... but it isn't.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

kirkevans (8/20/2015)
Thank you!
This is just what I need, with one small problem - I can't find the "Parameters" button that you mention.

Connection properties window.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

pietlinden SSC Guru Points: 63503 More actions · Answer 8

MM,

Any idea why the parameters button would be grayed out? I'm using Integrated Security, and the SQL Instance is on my local PC.

So I can't specify a parameter as (?).

(Oh, just to make things fun, since posting this originally, I upgraded to Office 2013.)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

pietlinden (8/23/2015)
MM,
Any idea why the parameters button would be grayed out? I'm using Integrated Security, and the SQL Instance is on my local PC.
So I can't specify a parameter as (?).
(Oh, just to make things fun, since posting this originally, I upgraded to Office 2013.)

The example I posted in my last post is using integrated security on the local PC. Make sure you are using Microsoft Query, not SQL Server as the connection type.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);