Run Sql Stored Procedure From Excel

[flickimp]

Hi

I want to create a Stored Procedure in SQL with three variables that are declared in an Excel file.

Thus, when the user enters the three variables into

Cells A1, B1, C1

Then hits a button....

The Stored procedure in SQL 2000 will run - and then return the results to a refreshable pivot table in the Excel file.

Any ideas?

[amitpatel66]

Hi

I want to create a Stored Procedure in SQL with three variables that are declared in an Excel file.

Thus, when the user enters the three variables into

Cells A1, B1, C1

Then hits a button....

The Stored procedure in SQL 2000 will run - and then return the results to a refreshable pivot table in the Excel file.

Any ideas?

Write an Excel Macro for this.
Connect to SQL Server from Excel and call a procedure as required.

[flickimp]

New to macros.

Any tips?

[amitpatel66]

New to macros.

Any tips?

You can find lots of examples by little web searching.
Kindly POST what you tried so that we could help in case of any problem!!

[flickimp]

Hi

Heres the Stored Procedure

Say in Excel ---- cell A1 = @Type and cell B1 = @LSS

Create Proc sp_Flickimp_test
@Type Varchar(3),
@LSS Varchar(3)
as
Select Provider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
Case When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
HRG_Code,
Specialised_Services_Code,
Local_AdmitUnit_Code,
Total_Spells, Tariff_Initial_Amount, Tariff_ShortStay_Spells,
Tariff_ShortStay_Adj, Tariff_SpecServ_Adj, Tariff_Final_Amount,
Tariff_LS_Spells, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payment,
Tariff_Total_Payment, Local_Total_Payment, Local_AdmitUnit_Adj

From tbl_BaseLine_UHL_Spells_Trend_0708 a

Left Outer Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

Left Outer Join dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

Where (Financial_year = '2006/07' and Financial_Month between '7' and '12')
or (Financial_year = '2007/08' and Financial_Month between '1' and '6')
and Commissioner_Code = @Type
and LSS_Flag = @LSS

[amitpatel66]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use the tags in future.

MODERATOR

[amitpatel66]

Hi

Heres the Stored Procedure

Say in Excel ---- cell A1 = @Type and cell B1 = @LSS

Create Proc sp_Flickimp_test
@Type Varchar(3),
@LSS Varchar(3)
as
Select Provider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
Case When Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
HRG_Code,
Specialised_Services_Code,
Local_AdmitUnit_Code,
Total_Spells, Tariff_Initial_Amount, Tariff_ShortStay_Spells,
Tariff_ShortStay_Adj, Tariff_SpecServ_Adj, Tariff_Final_Amount,
Tariff_LS_Spells, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payment,
Tariff_Total_Payment, Local_Total_Payment, Local_AdmitUnit_Adj

From tbl_BaseLine_UHL_Spells_Trend_0708 a

Left Outer Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

Left Outer Join dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

Where (Financial_year = '2006/07' and Financial_Month between '7' and '12')
or (Financial_year = '2007/08' and Financial_Month between '1' and '6')
and Commissioner_Code = @Type
and LSS_Flag = @LSS

Alright!! Did you try calling this from a Macro.
You need to connect to SQL server from Excel.

[flickimp]

Hi

Sorry for not using the tags.

The Stored procedure was created in SQL Query Analyzer 2000.

I tried to use MS Query to pull the SP back with the variables, but no joy.

What steps must I take to allow the parameters from excel cells to be picked up by the stored procedure so that it returns the results back to excel?