我有下面的复杂查询,它动态地构建列列表,然后将它们传递给第二个查询(使用透视)并执行它。
我在Visual Studio中生成了一个报表,它传递一个参数(@event),供用户选择一个要运行报表的事件。为了使@event在@sql语句的作用域中,它被连接为
'+ @Event +'
但是,当我运行语句时,我得到一个转换失败的错误
Conversion failed when converting the varchar value
如果我对@event的值进行硬编码,则查询将正确运行。
我做错了什么不让这一切继续下去?
谢谢
DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max)
DECLARE @select_list AS varchar(max)
SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + ']'
,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, PIVOT_CODE) + '] AS [' + CONVERT(varchar, PIVOT_NAME) + ']'
FROM (
SELECT DISTINCT PIVOT_CODE, PIVOT_NAME
FROM (
SELECT s.SESSION_REF AS PIVOT_CODE, s.name as PIVOT_NAME
FROM DELEGATE as d
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where ds.NO_DELEGATES=1 and d.EVENT_REF=@Event
) as rows
) AS PIVOT_CODES
SET @sql = '
;WITH p AS (
select d.DELEGATE_REF as REF, s.SESSION_REF AS PIVOT_CODE, d.name, ds.NO_DELEGATES
, o.COMPANY_NAME as ''Org'', l7.LOOKUP_FULL_DESC as ''Org_Member'', l8.LOOKUP_FULL_DESC as ''Org_Type''
, e.NAME as ''Event'', CAST(d.code AS INTEGER) as Delegate_No, d.member_ref as ''Record_No''
, i.SURNAME, i.FORENAMES, l6.LOOKUP_FULL_DESC as ''Status'', l4.LOOKUP_FULL_DESC as ''Membership'', i_fee.LABEL_NAME as ''Feepayer''
, CAST(d.COMMENT AS NVARCHAR(100)) as ''Comments'', l1.LOOKUP_FULL_DESC as ''Delegate_Type''
, e1.EMAIL_ADDRESS as ''Email''
--, CASE WHEN e1.type = 1236 then e1.EMAIL_ADDRESS WHEN e2.type = 1240 then e2.EMAIL_ADDRESS WHEN e3.type = 1197 then e3.EMAIL_ADDRESS ELSE NULL END as ''Email''
, d.mailing_different, l.ADDRESS1 as ''Mail1'', l.ADDRESS2 as ''Mail2'', l.ADDRESS3 as ''Mail3'', l.TOWN as ''Mail4'', l.POSTCODE as ''Mail5'', l.COUNTRY as ''Mail6''
, d.invoice_different, l2.ADDRESS1 as ''Inv1'', l2.ADDRESS2 as ''Inv2'', l2.ADDRESS3 as ''Inv3'', l2.TOWN as ''Inv4'', l2.POSTCODE as ''Inv5'', l2.COUNTRY as ''Inv6''
, d.TOTAL_AMOUNT, l5.LOOKUP_FULL_DESC as ''Pay''
, CAST(dq1.comments AS NVARCHAR(100)) as ''Q_B_Name'', CAST(dq2.comments AS NVARCHAR(100)) as ''Q_B_Inst'', a.DESCRIPTION as ''Q_Food'', CAST(dq3.comments AS NVARCHAR(100)) as ''Q_Food_Comment'', a2.DESCRIPTION as ''Q_Special'', CAST(dq4.comments AS NVARCHAR(100)) as ''Q_Special_Comment'', CAST(dq5.comments AS NVARCHAR(100)) as ''Q_Twitter'', CAST(dq6.comments AS NVARCHAR(100)) as ''Q_Number'', CAST(dq7.comments AS NVARCHAR(100)) as ''Q_School''
, l3.LOOKUP_FULL_DESC as ''Delegate_Status'', ev.DESCRIPTION as ''Session_Rate'', ev2.DESCRIPTION as ''Rate''
FROM DELEGATE as d
INNER JOIN EVENT as e on d.EVENT_REF=e.EVENT_REF
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN EVENT_RATE as ev on ds.EVENT_RATE_REF=ev.EVENT_RATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
INNER JOIN DELEGATE_SESSION as ds2 on d.DELEGATE_REF=ds2.DELEGATE_REF
INNER JOIN EVENT_RATE as ev2 on ds2.EVENT_RATE_REF=ev2.EVENT_RATE_REF
INNER JOIN EVENT as event2 on ev2.EVENT_REF=event2.EVENT_REF
LEFT JOIN LOCATION as l on d.MAILING_LOCATION=l.LOCATION_REF
LEFT JOIN LOCATION as l2 on d.INVOICE_LOCATION=l2.LOCATION_REF
INNER JOIN MEMBER as m on d.MEMBER_REF=m.MEMBER_REF
LEFT JOIN INDIVIDUAL as i on m.INDIVIDUAL_REF=i.INDIVIDUAL_REF
LEFT JOIN CONTACT as c on i.INDIVIDUAL_REF=c.INDIVIDUAL_REF and c.MAIN_ORGANISATION=''Y''
LEFT JOIN ORGANISATION as o on c.ORGANISATION_REF=o.ORGANISATION_REF and c.MAIN_ORGANISATION=''Y''
LEFT JOIN MEMBER as m2 on o.ORGANISATION_REF=m2.ORGANISATION_REF
LEFT JOIN LOOKUP as l7 on m2.MEMBER_STATUS=l7.LOOKUP_REF
LEFT JOIN ATTRIBUTE as at3 on o.ORGANISATION_REF=at3.ORGANISATION_REF and at3.CODE_TYPE=206 --School type
LEFT JOIN LOOKUP as l8 on at3.ATTR_CODE_REF=l8.LOOKUP_REF
LEFT JOIN LOOKUP as l1 on d.TYPE=l1.LOOKUP_REF
LEFT JOIN LOOKUP as l3 on d.STATUS=l3.LOOKUP_REF
LEFT JOIN LOOKUP as l6 on m.MEMBER_STATUS=l6.LOOKUP_REF
LEFT JOIN LOOKUP as l4 on m.MEMBER_CLASS=l4.LOOKUP_REF
LEFT JOIN LOOKUP as l5 on d.PAY_METHOD=l5.LOOKUP_REF
LEFT JOIN INDIVIDUAL as i_fee on d.FEEPAYING_MEMBER=i_fee.INDIVIDUAL_REF
--LEFT JOIN EMAIL as e1 on d.INDIVIDUAL_REF=e1.INDIVIDUAL_REF and (e1.MAIN_EMAIL=''Y'' and (e1.type=1236))
--LEFT JOIN EMAIL as e2 on d.INDIVIDUAL_REF=e2.INDIVIDUAL_REF and (e2.MAIN_EMAIL=''Y'' and (e2.type=1240))
--LEFT JOIN EMAIL as e3 on d.INDIVIDUAL_REF=e3.INDIVIDUAL_REF and (e3.MAIN_EMAIL=''Y'' and (e3.type=1197))
--LEFT JOIN EMAIL as e4 on d.INDIVIDUAL_REF=e3.INDIVIDUAL_REF and (e3.MAIN_EMAIL=''Y'' and (e3.type=2976))
LEFT JOIN EMAIL as e1 on i.INDIVIDUAL_REF=e1.INDIVIDUAL_REF and e1.MAIN_EMAIL=''Y'' and (e1.type NOT IN (2232,1241,1242,1106,3220,2612))
LEFT JOIN DELEGATE_QUESTION as dq1 on d.DELEGATE_REF=dq1.DELEGATE_REF and dq1.question=2054
LEFT JOIN DELEGATE_QUESTION as dq2 on d.DELEGATE_REF=dq2.DELEGATE_REF and dq2.question=2055
LEFT JOIN DELEGATE_QUESTION as dq3 on d.DELEGATE_REF=dq3.DELEGATE_REF and dq3.question=1620
LEFT JOIN DELEGATE_QUESTION as dq4 on d.DELEGATE_REF=dq4.DELEGATE_REF and dq4.question=1621
LEFT JOIN DELEGATE_QUESTION as dq5 on d.DELEGATE_REF=dq5.DELEGATE_REF and dq5.question=2626
LEFT JOIN DELEGATE_QUESTION as dq6 on d.DELEGATE_REF=dq6.DELEGATE_REF and dq6.question=3155
LEFT JOIN DELEGATE_QUESTION as dq7 on d.DELEGATE_REF=dq7.DELEGATE_REF and dq7.question=2979
LEFT JOIN ANSWER as a on dq3.ANSWER_REF=a.ANSWER_REF
LEFT JOIN ANSWER as a2 on dq4.ANSWER_REF=a2.ANSWER_REF
where ds.NO_DELEGATES=1 and d.EVENT_REF='+ @Event +'
)
SELECT event, delegate_no, record_no, Org, Org_Member, Org_Type, name, surname,forenames,status,membership,feepayer
, comments, Delegate_Type
, Email
, mailing_different, Mail1, Mail2, Mail3, Mail4, Mail5, Mail6, invoice_different, Inv1, Inv2, Inv3, Inv4, Inv5, Inv6
, total_amount, pay
, Q_B_Name, Q_B_Inst, Q_Food, Q_Food_Comment, Q_Special, Q_Special_Comment, Q_Twitter, Q_Number, Q_School, Delegate_Status, Rate
, ' + @select_list + '
FROM p
PIVOT (
MAX(Session_Rate)
FOR PIVOT_CODE IN (
' + @pivot_list + '
)
) AS pvt
'
EXEC (@sql)
