Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Parameter to a SQL query

Posted on 2007-11-21
9
Medium Priority
?
4,338 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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

578 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