sql - Excel 中的 SQL 子查询参数

Question

我有一个带有子查询的 SQL 查询，我想从 Excel 工作表中的单元格分配子查询参数，因为这个查询不能以图形方式表示 Excel 不断抛出错误：

[Microsoft][ODBC SQL Server Driver]无效的参数号和[Microsoft][ODBC SQL Server Driver]无效的描述符索引

我已经尝试过这里提到的解决方案来欺骗 excel 而没有成功Using Parameters in SQL query with sub-query

 ;WITH dataset_bl as (
SELECT 
--rank() over (partition by date_of_AC, ac_room order by  into_theatre) as OrderNumber
--,datetime_of_AC as booking_datetime
date_of_AC
,AC_room
,specialty
,OAMRN
,substring(Subject_NAME,1,CHARINDEX(' ',Subject_NAME,1)) as 'surname'
,substring(Subject_NAME,CHARINDEX(' ',Subject_NAME,1)+2,500) as 'forename'
,convert(date,[dttm_of_birth],103) as DOB
,PRIN_SO as 'surg'
,'"'+ASST_SO_1+' '+ASST_SO_2+'"' as 'assistant_SO'
,substring(CONSULT_NAME,1,abs(CHARINDEX(',',CONSULT_NAME,1)-1)) as 'consult_surname'
,substring(CONSULT_NAME,CHARINDEX(' ',CONSULT_NAME,1)+1,500) as 'consult_first_name'
,'"'+actual_AC+'"' as AC_details   
,'"'+PLANNED_PROD+'"' as booked_details
,'Carried Out' as 'TT_outcome'
, 'NULL' as  'unP_return_flag'
,OP_type
--,LOS
,case when CL_PRIORITY ='' then 'Not listed' else 'isted' end as islisted
,CLI_PRIORITY as listed_priority
,case when EM_PRIORITY like '<=%' then 'Submitted' else 'No Greensheet' end as isGreensheet
,em_priority as a, case when EM_PRIORITY like '<=%' and em_priority <>  '<=72hrsCannot charge before prod'then LEFT(EM_PRIORITY, abs(charindex('-',EM_PRIORITY)-1))  
 when em_priority = '<=72hrsCannot diso before prod' then '<=72hrs' else 'NULL' end as em_priority


FROM sample.dbo.tb_Fnl_Sur_th4 WITH (NOLOCK)
**WHERE main_ident = 'A224'  and  convert(date,into_Start, 103) >= '2019-07-01' and convert(date,into_Start, 103) <= '2019-07-31'** --i am trying to get these parameters from excel cell value
)
SELECT * FROM dataset_bl
WHERE specialty like 'abc%'
or (consult_surname like '%abc%' and consult_first_name like '%def%' )
    or surg in ('cde,fghi',
'jkl,'mnop,
'qrs,Tuv')


order by convert(date,date_of_procedure,103), operating_room--, into_theatre

标签： sqlsql-serverexcel

Answer 1

这里有一些建议。

首先是检查日期列的格式。

并建立你的参数就像

Cast(into_Start as smalldatetime) between ? and ?