Using SQL Server procedures from Excel

• Marty Sprague

  SSC Enthusiast

  Points: 176

  More actions

  November 20, 2008 at 12:54 pm

  #215538

  We have an existing excel 2003 spreadsheet which has an account number in each row. I have a sql server procedure (sql server 2005) that accepts an account number as input and returns the account status and insurance name from the database.

  I would like to execute the sql server procedure from the spreadsheet so it will read the account number from each row and place the return values (the account status and insurance name) from the procedure into the spreadsheet.

  Is this possible?

  Thanks in advance,

  marty Sprague

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  November 20, 2008 at 10:53 pm

  #901448

  Marty Sprague (11/20/2008)

  ---

  We have an existing excel 2003 spreadsheet which has an account number in each row. I have a sql server procedure (sql server 2005) that accepts an account number as input and returns the account status and insurance name from the database.

  I would like to execute the sql server procedure from the spreadsheet so it will read the account number from each row and place the return values (the account status and insurance name) from the procedure into the spreadsheet.

  Is this possible?

  Thanks in advance,

  marty Sprague

  In excel

  1. Data - > Import exernal data - > New database query

  2. select new data source

  3. select the name of the dsn

  4. select the type of the connection

  5. click on connect

  6 . provide server logins

  7. click on the options button to set the db name.

  8. click on ok button

  9. now you will come to the first screen

  10. double clck on the dsn you have created, a query editor will open

  11. do not add any table, click close

  12. now click on the SQL button and call the sp with parameters

  13. the results wil come in same window.

  14. click on return data button to return the results to the active worksheet

  kshitij kumar
  kshitij@krayknot.com
  www.krayknot.com

• Marty Sprague

  SSC Enthusiast

  Points: 176

  More actions

  November 21, 2008 at 9:25 am

  #901715

  Thanks for the quick response. What would be the correct syntax for the procedure?

  For example, The procedure has one input variable and two output variables. The input variable value to the procedure would be in column "D" of the spreadsheet starting in row 2. Since I need this executed for each row in the spreadsheet the column would remain as "E" but the row would change. The 2 output variables from the procedure would be placed in columns "K" and "L" in each row.

  Assuming the procedure name is my_procedure, what would the syntax be for the three variables?

  Thanks in advance!

  Marty