Solved

Excel Parameter to a SQL query

Posted on 2007-11-21
9
4,313 Views
Last Modified: 2012-06-21
How can I pass two parameters from an excel workbook into a sql query (specifically a view)?
0
Comment
Question by:gdspeare
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20329198
use a stored procedures, I dont thing a view is for passing parameters.
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20329201
and yes you can
0
 

Author Comment

by:gdspeare
ID: 20329266
Ok - thanks for telling me I can would you tell me how?
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20329378
obtain the values with tsql

SELECT * FROM OpenRowset
('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=c:\temp\MyWS',
'SELECT * FROM [sheet$]')

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20329534
>>thanks for telling me I can would you tell me how?<<
Can you post your code as it stands now?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20329548
Also, the following question appears to have fallen through the cracks and is now considered abandoned, please attend to it:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22809159.html
0
 

Author Comment

by:gdspeare
ID: 20329789
I have created the sp.  The variables would be the two where conditions...

CREATE PROCEDURE spMEDICINE_VISITS_BY_DIVISION

AS

Select            Actual_Department_Number,
            FYear,
            Appt_Period_Part,
CASE            Department_Names
                  WHEN 'UTM IDX - Cardiology Clinic' THEN 'Cardiology'
                  WHEN 'UTM IDX - CH-RHEUMATOLOGY' THEN 'Rheum/Immun'
                  WHEN 'UTM IDX - UC-ENDOCRINOLOGY/DIABETES' THEN 'Endocrine'
                  WHEN 'UTM EPIC - DP PULMONARY DISEASES' THEN 'Pulmonary'
                  WHEN 'UTM IDX - Pulmonary Disease' THEN 'Pulmonary'
                  WHEN 'UTM EPIC - SNW1 PULMONARY DISEASE' THEN 'Pulmonary'
                  WHEN 'UTM IDX - UC-GERIATRICS & GERONTOLOGY' THEN 'Geriatrics'
                  WHEN 'UTM EPIC - DP RHEUMATOLOGY/IMMUNO' THEN 'Rheum/Immun'
                  WHEN 'UTM IDX - Neurology' THEN 'Neurology'
                  WHEN 'UTM IDX - UC-NEPHROLOGY' THEN 'Nephrology'
                  WHEN 'UTM EPIC - DP NEPHROLOGY' THEN 'Nephrology'
                  WHEN 'UTM IDX - Gastroenterology' THEN 'Gastro'
                  WHEN 'UTM EPIC - DP NEUROLOGY' THEN 'Neurology'
                  WHEN 'UTM IDX - UC-CARDIOLOGY' THEN 'Cardiology'
                  WHEN 'UTM IDX - UC-NEUROLOGY' THEN 'Neurology'
                  WHEN 'UTM IDX - Geriatrics' THEN 'Geriatrics'
                  WHEN 'UTM IDX - UC-GASTROENTEROLOGY' THEN 'Gastro'
                  WHEN 'UTM IDX - Clinical Immunology' THEN 'Rheum/Immun'
                  WHEN 'UTM IDX - UC-MDA NEUROLOGY CLINIC' THEN 'Neurology'
                  WHEN 'UTM EPIC - DP ALLERGY' THEN 'Allergy'
                  WHEN 'UTM IDX - Diabetes' THEN 'Diabetes'
                  WHEN 'UTM EPIC - DP GERIATRICS MEDICINE' THEN 'Geriatrics'
                  WHEN 'UTM EPIC - SNW1 RHEUMATOLOGY/IMMU' THEN 'Rheum/Immun'
                  WHEN 'UTM IDX - Heart Station' THEN 'OMIT'
                  WHEN 'UTM EPIC - DP GASTROENTEROLOGY' THEN 'Gastro'
                  WHEN 'UTM IDX - SR-GI AMBULATORY CENTER' THEN 'OMIT'
                  WHEN 'UTM EPIC - DP CLINICAL PHARMACOLO' THEN 'Pharmacology'
                  WHEN 'UTM IDX - Clinical Pharmacology' THEN 'Pharmacology'
                  WHEN 'UTM EPIC - DP INFECTIOUS DISEASES' THEN 'Infectous Diseases'
                  WHEN 'UTM IDX - SR-CARDIOLOGY' THEN 'Cardiology'
                  WHEN 'UTM EPIC - TDI NEPHROLOGY' THEN 'Nephrology'
                  WHEN 'UTM EPIC - DP CARDIOLOGY' THEN 'Cardiology'
                  WHEN 'UTM IDX - UC-INFECTIOUS DISEASE' THEN 'Infectous Diseases'
                  WHEN 'UTM IDX - SR-CATH LAB PROCEDURE(CARD)' THEN 'OMIT'
                  WHEN 'UTM IDX - CH-DERMATOLOGY ADULT' THEN 'Dermatology'
                  WHEN 'UTM IDX - Nephrology' THEN 'Nephrology'
                  WHEN 'UTM IDX - CH-RENAL ADULT' THEN 'Diabetes'
                  WHEN 'UTM IDX - UC-LUPUS/VASCULITIS RHEUM' THEN 'Rheum/Immun'
                  WHEN 'UTM IDX - SR-CLINIC IMMUN/RHEUMATOLOGY' THEN 'Rheum/Immun'
                  WHEN 'UTM EPIC - SNW2 GASTROENTEROLOGY' THEN 'Gastro'
                  WHEN 'UTM EPIC - UHC NEUROLOGY' THEN 'Neurology'
                  WHEN 'UTM IDX - SR-NEUROLOGY' THEN 'Neurology'
                  WHEN 'UTM IDX - UC-INTERNAL MEDICINE' THEN 'Internal Medicine'
                  WHEN 'UTM IDX - Internal Medicine' THEN 'Internal Medicine'
                  WHEN 'UTM IDX - SR-PULMONARY DISEASE' THEN 'Pulmonary'
                  WHEN 'UTM EPIC - DP INTERNAL MEDICINE' THEN 'Internal Medicine'
                  WHEN 'UTM IDX - Infectious Disease' THEN 'Infectous Diseases'
                  WHEN 'UTM EPIC - SNW1 CARDIOLOGY' THEN 'Cardiology'
                  WHEN 'UTM IDX - UC-CLINIC IMMUN/RHEUMATOLOGY' THEN 'Rheum/Immun'
                  WHEN 'UTM EPIC - TDI DERMATOLOGY' THEN 'Dermatology'
                  ELSE 'NOT IDENTIFIED'
            END AS DIVISION,
            Provider_Names,
            SUM(isnull(SumofArrived,0)) AS Total_Arrived,
                  
            Location_Name,
            Provider_Category_Name
from            schedulingdashboardsubtableall
Where             
            (actual_department_number = '10') AND
            (appt_period_part = '200710')
Group By      Actual_Department_Number,      
            FYear,
            Appt_Period_Part,
            Department_Names,
            Provider_Names,
            Location_Name,
            Provider_Category_Name
0
 
LVL 18

Accepted Solution

by:
Yveau earned 75 total points
ID: 20330256
... consider a lookup table in stead of the endless case when end. That should be a lot faster, better to maintain and a lot more flexible !

Try this code:

Hope this helps ...
CREATE PROCEDURE spMEDICINE_VISITS_BY_DIVISION

 @DepNr varchar(3)

,@Period varchar(6)
 

AS
 

Select            Actual_Department_Number,

            FYear,

            Appt_Period_Part,

CASE            Department_Names

                  WHEN ...

                  ELSE 'NOT IDENTIFIED'

            END AS DIVISION,

            Provider_Names,

            SUM(isnull(SumofArrived,0)) AS Total_Arrived,

                  

            Location_Name,

            Provider_Category_Name

from            schedulingdashboardsubtableall

Where             

            (actual_department_number = @DepNr) AND

            (appt_period_part = @Period)

Group By      Actual_Department_Number,      

            FYear,

            Appt_Period_Part,

            Department_Names,

            Provider_Names,

            Location_Name,

            Provider_Category_Name

Open in new window

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20330715
Glad I could be of any help and thanks for the grade !
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now