• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4363
  • Last Modified:

Excel Parameter to a SQL query

How can I pass two parameters from an excel workbook into a sql query (specifically a view)?
0
gdspeare
Asked:
gdspeare
  • 3
  • 2
  • 2
  • +1
1 Solution
 
SQL_SERVER_DBACommented:
use a stored procedures, I dont thing a view is for passing parameters.
0
 
SQL_SERVER_DBACommented:
and yes you can
0
 
gdspeareAuthor Commented:
Ok - thanks for telling me I can would you tell me how?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
SQL_SERVER_DBACommented:
obtain the values with tsql

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

0
 
Anthony PerkinsCommented:
>>thanks for telling me I can would you tell me how?<<
Can you post your code as it stands now?
0
 
Anthony PerkinsCommented:
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
 
gdspeareAuthor Commented:
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
 
YveauCommented:
... 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
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now