GPSPOW
asked on
Passing Excel values to a SQL Server 2012 view
I am using Excel 2010 to invoke a SQL view to populate a table into Sheet1. The parameter values are in cells B1 and B2.
The following is the view code I am using:
SELECT CASE WHEN AttendProviderID IS NOT NULL THEN AttendProviderID ELSE CASE WHEN PrimCareProviderID IS NOT NULL
THEN PrimCareProviderID ELSE CASE WHEN AdmitProviderID IS NOT NULL THEN AdmitProviderID ELSE CASE WHEN ErProviderID IS NOT NULL
THEN ErProviderID ELSE CASE WHEN FamilyProviderID IS NOT NULL THEN FamilyProviderID ELSE '' END END END END END AS ProvID,
dbo.BarVisits.VisitID, dbo.BarVisits.AccountNumbe r, CASE WHEN dbo.BarVisits.DischargeDat eTime IS NULL
THEN dbo.BarVisits.ServiceDateT ime ELSE dbo.BarVisits.DischargeDat eTime END AS DisDt, CASE WHEN dbo.BarVisits.AdmitDateTim e IS NULL
THEN dbo.BarVisits.ServiceDateT ime ELSE dbo.BarVisits.AdmitDateTim e END AS AdmDt, dbo.BarVisits.InpatientOrO utpatient,
dbo.BarVisits.InpatientSer viceID, dbo.AbsConsultations.Consu ltProvider ID, dbo.AbsConsultations.Consu ltSeqID
FROM dbo.BarVisitFinancialData2 RIGHT OUTER JOIN
dbo.AbsConsultations RIGHT OUTER JOIN
dbo.BarVisits ON dbo.AbsConsultations.Visit ID = dbo.BarVisits.VisitID ON dbo.BarVisitFinancialData2 .VisitID = dbo.BarVisits.VisitID
WHERE (CASE WHEN dbo.BarVisits.DischargeDat eTime IS NULL THEN dbo.BarVisits.ServiceDateT ime ELSE dbo.BarVisits.DischargeDat eTime END BETWEEN
sheet1.range("B1").value AND sheet1.range("B2").value)
When the date parameters are hard coded, i.e 2014-10-01 and 2014-10-31, it works fine.
The where clause is not working. Can someone look at it and let me know what I need to correct?
The following is the view code I am using:
SELECT CASE WHEN AttendProviderID IS NOT NULL THEN AttendProviderID ELSE CASE WHEN PrimCareProviderID IS NOT NULL
THEN PrimCareProviderID ELSE CASE WHEN AdmitProviderID IS NOT NULL THEN AdmitProviderID ELSE CASE WHEN ErProviderID IS NOT NULL
THEN ErProviderID ELSE CASE WHEN FamilyProviderID IS NOT NULL THEN FamilyProviderID ELSE '' END END END END END AS ProvID,
dbo.BarVisits.VisitID, dbo.BarVisits.AccountNumbe
THEN dbo.BarVisits.ServiceDateT
THEN dbo.BarVisits.ServiceDateT
dbo.BarVisits.InpatientSer
FROM dbo.BarVisitFinancialData2
dbo.AbsConsultations RIGHT OUTER JOIN
dbo.BarVisits ON dbo.AbsConsultations.Visit
WHERE (CASE WHEN dbo.BarVisits.DischargeDat
sheet1.range("B1").value AND sheet1.range("B2").value)
When the date parameters are hard coded, i.e 2014-10-01 and 2014-10-31, it works fine.
The where clause is not working. Can someone look at it and let me know what I need to correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The stored procedure works great.
However, it only works on my login. I will need to speak with the DBA and get the users the proper access.
Thanks
Glen
However, it only works on my login. I will need to speak with the DBA and get the users the proper access.
Thanks
Glen
Add the following line after the create statement of the procedure, before "as" but after the parameter declaration:
This will execute the procedure with your rights rather than the person running it. It prevents you from having to give everyone rights to the database. Just make sure to secure the file to ensure non-authorized people can't execute it.
with execute as owner
This will execute the procedure with your rights rather than the person running it. It prevents you from having to give everyone rights to the database. Just make sure to secure the file to ensure non-authorized people can't execute it.
ASKER
Cool..
Thanks
Thanks
https://www.experts-exchange.com/questions/28330667/Excel-SQL-Query-of-an-External-SQL-table-with-parameters.html