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?
Quote:
Originally Posted by 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?
Write an Excel Macro for this.
Connect to SQL Server from Excel and call a procedure as required.|||New to macros.
Any tips?|||
Quote:
Originally Posted by flickimp
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!!|||Hi
Heres the Stored Procedure
Say in Excel -- cell A1 = @.Type and cell B1 = @.LSS
Create Proc sp_Flickimp_test
@.TypeVarchar(3),
@.LSSVarchar(3)
as
SelectProvider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
CaseWhen 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
Fromtbl_BaseLine_UHL_Spells_Trend_0708 a
LeftOuter Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code
LeftOuter 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')
andCommissioner_Code = @.Type
andLSS_Flag = @.LSS|||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|||
Quote:
Originally Posted by flickimp
Hi
Heres the Stored Procedure
Say in Excel -- cell A1 = @.Type and cell B1 = @.LSS
Create Proc sp_Flickimp_test
@.TypeVarchar(3),
@.LSSVarchar(3)
as
SelectProvider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
CaseWhen 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
Fromtbl_BaseLine_UHL_Spells_Trend_0708 a
LeftOuter Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code
LeftOuter 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')
andCommissioner_Code = @.Type
andLSS_Flag = @.LSS
Alright!! Did you try calling this from a Macro.
You need to connect to SQL server from Excel.|||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?
