Post reply

Passing Parameter from Excel to SQL 2008 Stored Procedure using VBA

  • March 8, 2010 at 7:41 am

    #217503

    I am working on a spreadsheet that gets data from an SQL server using a stored procedure and parameters. One of the parameters needs to be OR'd if multiple values are selected in a listbox.

    Possible values for the variable (sql) used for parameter @station include 'Battery Heights' 'Airport Sub' 'Micron 600' 'Micron 601'

    Here is the sql statement that I am using in Excel VBA:

    stSQL = "exec LoadData @station = " & sql & ", @g_s_date = '" & g_s_date & "', @g_e_date = '" _

    & g_e_date & "'"

    Here is the stored procedure (yes it says alter because I am trying to work on it):

    ALTER procedure [dbo].[LoadData] @station varchar(1000), @g_s_date date, @g_e_date date

    as

    create table #TempLoads (

    dateonly date,

    datafld float )

    Insert into #TempLoads (dateonly ,datafld )

    SELECT distinct convert(date, meter_data_dmd_time, 101),

    sum(meter_data1)

    from Meter_data_table where (station = @station) and

    meter_data_dmd_time between @g_s_date and @g_e_date

    group by meter_data_dmd_time

    select distinct CONVERT(datetime, dateonly, 101) as dateonly, MAX(datafld) from #TempLoads

    group by dateonly

    order by dateonly

    drop table #TempLoads

    I use a series of IF..Then statements in Excel VBA to build the (sql) variable to look like either 'Airport Sub' (for a single choice) or the variable can look like this with multiple choices... 'Airport Sub' or station = 'Battery Heights'

    I figured since the stored procedure already contained the first [station = ] that having the @station parameter continue with the OR's would be fine. This stored procedure works for one variable, but not multiple.

    Any ideas? Can this be done?

  • March 8, 2010 at 9:45 am

    #1130118

    you could try declaring the insert statement into a large varchar variable and then executing the whole string by using Exec(yourVariable) within your stored procedure.

    you could then change the where clause to use the in statement and change the way the parameter is passed. for example, if two options are selected then you would pass "'Battery Heights', 'Micron 600'".

    you need to remember to pass the quotation marks through with the strings when doing this, and you may want to test the large varchar variable before executing it by printing it and seeing the the text printed in SSMS to make sure it looks like a valid sql statement.

    you will have to watch for the quotation marks, especially as you need to include these within a varchar variable.

    let me know if i need to explain further.

  • March 8, 2010 at 9:53 am

    #1130124

    Yep, I had searched a few examples where folks have built an sql statement like this and was hoping there would be something easier. I know what you mean about the quotes, I get frustrated trying to follow all those and sometimes the parens too when I get a complicated calculation.

    I had tried to a station like @station and even station in @station but neither of those worked. I've been doing my testing directly in SQL so I don't keep getting kicked out of VBA with bugs.

    Thanks for the tip and I'll let you know if I need a hand.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply