Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Parameter to a SQL query

Posted on 2007-11-21
9
Medium Priority
?
4,332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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 300 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 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