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_tes t
@Type Varchar(3),
@LSS Varchar(3)
as
Select Provider_Code,
Commissioner_Co de,
LSS_Flag,
Financial_Year,
Financial_Month ,
Specialty_Code,
Description_Con tract,
Activity_Type,
Case When Activity_Type in ('Emergency','N on-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_G rouped,
HRG_Code,
Specialised_Ser vices_Code,
Local_AdmitUnit _Code,
Total_Spells, Tariff_Initial_ Amount, Tariff_ShortSta y_Spells,
Tariff_ShortSta y_Adj, Tariff_SpecServ _Adj, Tariff_Final_Am ount,
Tariff_LS_Spell s, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payme nt,
Tariff_Total_Pa yment, Local_Total_Pay ment, Local_AdmitUnit _Adj

From tbl_BaseLine_UH L_Spells_Trend_ 0708 a

Left Outer Join dbo.tbl_Refs_Sp ecialty_UHL b
on a.Specialty_Cod e = b.Code

Left Outer Join dbo.tbl_Refs_IP _SpecServ_0708 c
on a.Specialised_S ervices_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_Co de = @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_tes t
@Type Varchar(3),
@LSS Varchar(3)
as
Select Provider_Code,
Commissioner_Co de,
LSS_Flag,
Financial_Year,
Financial_Month ,
Specialty_Code,
Description_Con tract,
Activity_Type,
Case When Activity_Type in ('Emergency','N on-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_G rouped,
HRG_Code,
Specialised_Ser vices_Code,
Local_AdmitUnit _Code,
Total_Spells, Tariff_Initial_ Amount, Tariff_ShortSta y_Spells,
Tariff_ShortSta y_Adj, Tariff_SpecServ _Adj, Tariff_Final_Am ount,
Tariff_LS_Spell s, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payme nt,
Tariff_Total_Pa yment, Local_Total_Pay ment, Local_AdmitUnit _Adj

From tbl_BaseLine_UH L_Spells_Trend_ 0708 a

Left Outer Join dbo.tbl_Refs_Sp ecialty_UHL b
on a.Specialty_Cod e = b.Code

Left Outer Join dbo.tbl_Refs_IP _SpecServ_0708 c
on a.Specialised_S ervices_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_Co de = @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?