Solved

Excel Parameter to a SQL query

Posted on 2007-11-21
9
4,318 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

685 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