Script for Excel VBA to use a Stored Procedure with an Input Parameter

See if this will work:

Private Sub CommandButton1_Click()


' Declare the QueryTable object
Dim qt As QueryTable

' Set up the SQL Statement
sqlstring = "Execute GLDrillDown(" & format(inputbox("Enter Date"),"mm/dd/yyyy") & ")"

' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
 "ODBC;DSN=SysproCompany1"

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
End With


End Sub

Select allOpen in new window

Hi MWGAINES,

The "Execute GLDrillDown(" & format(inputbox("Enter Date"),"mm/dd/yyyy") & ")"

@EnterYear is a integer field.. Does this matter??

Oops, was thinking date, not year. ok change it to:
 "Execute GLDrillDown(" & inputbox("Enter Year") & ")"

Here is some code I use which calls a stored procedure.

The data comes from a workbook and I'm updating the DB with the entered data.

So, a reverse of what you are doing, but does describe how to call a stored procedure with parameters.

The important bit is the comment.

' Stored procedures are methods of the connection.

Private Sub CommandButton1_Click()
    Dim o_Conn As ADODB.Connection
    Dim i_BlankCount As Integer
    Dim i_CurrentRow As Integer
    Dim s_Updated As String
    
    Application.ScreenUpdating = False
    
    i_BlankCount = 0
    i_CurrentRow = 5
    Set o_Conn = New ADODB.Connection
    s_Updated = Format(Date, "YYYY-MM-DD")
    
    With o_Conn
        .Open "Accounts" ' Assuming that Windows Authentication is now running.
        .DefaultDatabase = "RechargePriceMatrix"
        
        While i_BlankCount < 2
            s_Database = Cells(i_CurrentRow, 1).Value
            s_Contract = Cells(i_CurrentRow, 2).Value
            i_Year = Cells(i_CurrentRow, 3).Value
            i_Period = Cells(i_CurrentRow, 4).Value
            i_Week = Cells(i_CurrentRow, 5).Value
            s_Start = Format(Cells(i_CurrentRow, 6).Value2, "YYYY-MM-DD")
            If s_Database = "" Or s_Contract = "" Then
                i_BlankCount = 1 + i_BlankCount
            Else
                i_BlankCount = 0
                If i_Year >= 2008 And i_Year <= 2020 And _
                    i_Period >= 1 And i_Period <= 13 And _
                    i_Week >= 0 And i_Week <= 5 Then
                    ' Stored procedures are methods of the connection!
                    .sp_SetPeriodOpen s_Database, s_Contract, i_Year, i_Period, i_Week, s_Start, s_Updated
                End If
            End If
            i_CurrentRow = 1 + i_CurrentRow
        Wend

        .Close
    End With

    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Hi RQuadring,

Can i assume that
s_Database
s_Contract
i_Year
i_Period
i_Week
s_Start
are all parameters of the Stored Procedure??  I am a bit confused... but I will attempt to work on it

MWGaines,

I did what our suggestion was...It didnt return any data

Executing a stored procedure won't return any data.  You would need a select statement for that....

Yes they are, sorry. They come from the data entered by the user into the worksheet.

I am essentially passing 1 worksheet row at a time to the database.

It is extremely easy.

My example may not be directly useful to you in this question, but it does mean you do not have to build the query and have to deal with the issues of mistyped data.






@MWGainesJR. I disagree.

A stored procedure may do anything. Including return multiple result sets.

RQualing, Using a Query table isn't going to return the data from the SP, they would need to use selects, to my knowledge, atleast.  He would need to use ADO or DAO to return the resulting tows to a recordset.....
 

Ah. I see the distinction. It isn't that SP can't return data, it may just not be read by Excel.

Hmmm.

I'd say that there is no reason why it would be any different.

The QueryTable returns the result set.

The result set is generated by the SP.

Whilst  INSERT, DELETE and UPDATE don't return data (well, not a result set), a SP can and a SELECT does.

I think it'd be fine.

Live the Answers but not sure where that leaves me.. Should I re-do my VB script?? or Do I alter it??

I am a rookie obviously

What does the stored procedure actually do?

If it's nothing too spectacular why not just use it's SQL in your code?

That's upto you.

Which do you feel more comfortable with. Which will you still understand in several months time.

Whilst my example was VBA for Excel, the code will translate to straight VB. Getting the parameters is obviously different, but that's not going to impact on the call to the database.

The query in excel only executes the code and returns the imediate result.  An SP is simply a call.  The immediate result can't be returned in this way.  
You would need to use DAO and utilize the ReturnRows property of the QueryDef.  

Is your SP a simple Select?  If so, just use the Statement instead of the SP call.  
Or I can help you return rows using the SP by using DAO.

If the SP returns data, it is no different to SELECT statement.

I've attached a spreadsheet which connects to our of our SQL servers and runs the stored procedure master..sp_databases.

Code included below

As you can see, the SP, creates a temp table, inserts some data into it and returns it to the client.

The spreadsheet is using the following SQL statement to get the data ...

{Call sp_databases(@RETURN_VALUE, @RETURN_VALUE)}

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_databases]    Script Date: 09/22/2010 13:46:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*	Procedure for pre-7.0 server */
ALTER proc [dbo].[sp_databases]
as
	set nocount on
	/* Use temporary table to sum up database size w/o using group by */
	create table #databases (
				  DATABASE_NAME varchar(32) NOT NULL,
				  size int NOT NULL)

	/* Insert row for each database */
	insert into #databases
		select
			name,
			(select sum(size) from master.dbo.sysusages
				where dbid = d.dbid)
		from master.dbo.sysdatabases d

	select
		 DATABASE_NAME,
		 DATABASE_SIZE = size*2,	/* Convert from 2048 byte pages to K */
		 REMARKS = convert(varchar(254),null)	/* Remarks are NULL */
	from #databases
	order by 1

Select allOpen in new window

CallingSP.xls
CallingSP.xml

MWGaines,

That would be ideal...I guess I needed clarity on how to use my SP with Parameters and utilize VB in Excel to return Data per entry points in a form of some sort...My lack of knowledge here is pretty disappointing, oh well...Can we try the DAO method..

Can you post the code that is in the SP?

USE [SysproCompany1]
GO
/****** Object:  StoredProcedure [dbo].[GLDrillDown]    Script Date: 09/22/2010 07:53:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[GLDrillDown]

      
@EnterYear as int

as



SELECT     Company, GlCode, BeginYearBalance, ClosingBalPer1, ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, ClosingBalPer6,
                      ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS GlPeriod,
                      'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear
FROM         dbo.GenHistory
Where GlYear = @EnterYear



UNION
SELECT     Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4,
                      0 AS ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10,
                      0 AS ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, Source, Journal, SUM(EntryValue) AS EntryValues, GlYear
FROM         dbo.GenTransaction
Where GlYear = @EnterYear

That second select should have a GROUP BY clause.

At a guess ...

GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear

oops,  correcting the returns

Private Sub CommandButton1_Click()  
  
  
' Declare the QueryTable object  
Dim qt As QueryTable  
Dim EnterYear as long  
EnterYear = InputBox("Enter Year")  
' Set up the SQL Statement  
sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory Where GlYear = " & EnterYear _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _
& "dbo.GenTransaction Where GlYear = " & EnterYear
  
' Set up the connection string, reference an ODBC connection  
' There are several ways to do this  
' Leave the name and password blank for NT authentication  
connstring = _  
 "ODBC;DSN=SysproCompany1"  
  
' Now implement the connection, run the query, and add  
' the results to the spreadsheet starting at row A1  
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)  
 .Refresh  
End With  
  
  
End Sub

Select allOpen in new window

Why no GROUP BY?

Taking a random table ...

SELECT
  agent_start_date,
  SUM(session_id)
FROM
  msdb.dbo.syssessions


results in ...


Msg 8120, Level 16, State 1, Line 1
Column 'msdb.dbo.syssessions.agent_start_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

RQuadling,

There was.. I have edited it multiple times due to the fact that it wasnt working in VB...So i figured that I would retype it.. Just forgot the group by.. I realized that after i sent it...

Just add the group by into the select statement in the vba code.....

OK. Just making sure. Sometimes the most obvious things can be overlooked.

MWGaines,

Getting a complie error... (cant see the error but thats what it says).. Included in image


Compile-error.JPG

Several problems:
1.  You need a space in front of the first WHERE
2.  You need a space in front of the first GROUP BY
3.  You need space in front of the second WHERE
4.  You need to remove the quote behind the second ENTERYEAR
5.  You need a space in front of the second GROUP BY

sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _  
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _  
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _  
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _  
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _  
& "FROM dbo.GenHistory" _
& " Where GlYear = " & EnterYear _ 
& " GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _ 
& " UNION " _  
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _  
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _  
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _  
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _  
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _  
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _  
& "dbo.GenTransaction Where GlYear = " & EnterYear _
& " Where GlYear = " & EnterYear _
& " GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

Select allOpen in new window

Get a where error....
Where-error.JPG

oops....forgot the single quotes before and after the variable:
 

sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _    
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _    
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _    
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _    
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _    
& "FROM dbo.GenHistory" _  
& " Where GlYear = '" & EnterYear _   
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _   
& " UNION " _    
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _    
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _    
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _    
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _    
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _    
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _    
& "dbo.GenTransaction Where GlYear = " & EnterYear _  
& " Where GlYear = '" & EnterYear _  
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

Select allOpen in new window

Still the same error...

I adjusted per your notes...

post your code again.  I see nothing wrong with the syntax.....

Private Sub CommandButton1_Click()
   
' Declare the QueryTable object
Dim qt As QueryTable
Dim EnterYear As Long
EnterYear = InputBox("Enter Year")
' Set up the SQL Statement
sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory" _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _
& "dbo.GenTransaction Where GlYear = " & EnterYear _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"




' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
 "ODBC;DSN=SysproCompany1"
 
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
 .Refresh
End With
 
 
End Sub

You've got 2 WHERE clauses ...

& "dbo.GenTransaction Where GlYear = " & EnterYear _
& " Where GlYear = '" & EnterYear _

Change that to ...

& "dbo.GenTransaction" &  _
& " Where GlYear = '" & EnterYear _

You have two Where clauses in the second select.....I might have added a second on accident:
 

sqlstring = "SELECT Company, GlCode, BeginYearBalance, ClosingBalPer1, " _
& "ClosingBalPer2, ClosingBalPer3, ClosingBalPer4, ClosingBalPer5, " _
& "ClosingBalPer6, ClosingBalPer7, ClosingBalPer8, ClosingBalPer9, " _
& "ClosingBalPer10, ClosingBalPer11, ClosingBalPer12, ClosingBalPer13, 0 AS " _
& "GlPeriod, 'BA' AS Source, 0 AS Journal, 0 AS EntryValue, GlYear " _
& "FROM dbo.GenHistory" _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear" _
& " UNION " _
& "SELECT Company, GlCode, 0 AS BeginYearBalance, 0 AS ClosingBalPer1, 0 AS " _
& "ClosingBalPer2, 0 AS ClosingBalPer3, 0 AS ClosingBalPer4, 0 AS " _
& "ClosingBalPer5, 0 AS ClosingBalPer6, 0 AS ClosingBalPer7, 0 AS " _
& "ClosingBalPer8, 0 AS ClosingBalPer9, 0 AS ClosingBalPer10, 0 AS " _
& "ClosingBalPer11, 0 AS ClosingBalPer12, 0 AS ClosingBalPer13, GlPeriod, " _
& "Source, Journal, SUM(EntryValue) AS EntryValues, GlYear FROM " _
& "dbo.GenTransaction" _
& " Where GlYear = '" & EnterYear _
& "' GROUP BY Company, GLCode, GLPeriod, Source, Journal, GLYear"

Select allOpen in new window

Getting a year error.... Sql sees the data type for the year as Decimal(4,0) should it be something other then Long as the datatype in VB??


Year-error.JPG

Whats the data type for the year in sql?

If it's text/varchar, leave the single quotes
If it's not text/varchar, take the single quotes out
I don't think it's a data type issue or else it wouldn't be a syntax error, it would be a mismatch error.
Put the text into an msgbox and stare over it a few minutes....if it's syntax it should be easy to see from there.

You need a zero in the GROUP BY clause for each field that is zero in the SELECT clause.

18:  " GROUP BY Company, G1Code, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, Source, Journal, G1Year"

It works!!! hallelujah (or however you spell it)!!!

Both experts were very patient and knowledgeable.. Really appreciate their time and energy