Reputation: 159
I have the following complex query which builds a list of columns dynamically before passing them to a second query (with a pivot) and executing it.
I have produced a report in Visual Studio that passes a parameter (@Event) for the user to select an event they want a report to run on. In order for the @Event to be in scope with the @SQL statement it is concatenated as
'+ @Event +'
however when I then run the statement I get a conversion failed error
Conversion failed when converting the varchar value
if I hardcode the values for @Event the query runs correctly.
What have I done wrong that wont let this run?
thanks
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)
Upvotes: 0
Views: 34
Reputation: 95561
Don't concatenate your values, use parametrised SQL. Concatenating like that is bad, as it leaves you open to injection.
Change your WHERE
clause to d.EVENT_REF= @dEvent
and then change your EXEC
to:
EXEC sp_executesql @SQL, N'@dEvent int', @dEvent = @Event;
Note, I have guess yoru datatype, as I can't see a DECLARE
in your provided SQL.
Also, instead of using code like '[' + CONVERT(varchar, PIVOT_CODE) + ']'
use QUOTENAME(PIVOT_CODE)
. That'll cope with what ever value is passed far better than the former. For example, if someone (silly enough) ever creates an object with a ]
in the name, then '[' + CONVERT(varchar, PIVOT_CODE) + ']'
would fail.
Helpful tip: When writing Dyanmic SQL, format it as well. it'll make things far easier to debug. Just because your SQL is dynamic doesn't mean you should forget simple things like good use of whitespace and linebreaks.
Upvotes: 2