June 28, 2019 at 9:09 am
I keep getting an error and when I click on the error, it brings me to @PropertyTable on the PropertyName object.
Any ideas whats up with this code?
DROP TABLE #DATA
DECLARE @DocNum VARCHAR(100), @DocVersion VARCHAR(10)
SET@DocNum = 'IQ-0005'
SET @DocVersion = '0.1'
SET NOCOUNT ON;
DECLARE @DocNumSrch NVARCHAR(MAX) = '%' + @DocNum + '%'
DECLARE @PivotProperties VARCHAR(1000)
DECLARE @SQL NVARCHAR(MAX)
CREATE TABLE #Data (
TestCaseIdINT,
TestCaseNumberNVARCHAR(MAX),
PropertyNameNVARCHAR(MAX),
PropertyValueNVARCHAR(MAX),
DispositionNVARCHAR(MAX)
)
DECLARE @PropertyId TABLE(
PropertyElementIdINT,
PropertyNameNVARCHAR(MAX)
)
INSERT INTO @PropertyId
SELECT DISTINCT
op.PropertyElementId,
op.PropertyName
FROM
dbo.ObjectProperties AS op
WHERE
PropertyName IN ( SELECT SETTINGVALUE FROM dbo.Reporting_Configuration WHERE ReportName = 'Incident Summary')
-- Pivot Properties
SET @PivotProperties = (
SELECT DISTINCT
STUFF((SELECT distinct '],[' + rc.SettingValue
FROM
dbo.Reporting_Configuration AS rc
WHERE
rc.ReportName = 'Incident Summary' AND
rc.SettingName IN ('Test' , 'Description')
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') + ']' ObjectProperties
from Reporting_Configuration rc)
INSERT INTO #Data
SELECT
d.DocumentId,
d.DocumentNumber,
o.PropertyName,
o.PropertyValue,
d.DocumentCurrentStatus
FROM
dbo.ObjectProperties AS o
JOIN dbo.Documents AS d ON
o.PropertyObjectId = d.DocumentObjectId
JOIN @PropertyId AS prop ON
o.PropertyElementId = prop.PropertyElementId
WHERE
(d.DocumentNumber LIKE @DocNumSrch) AND
(d.DocumentVersion = @DocVersion)
-- Pivot the field properties. Use Dynamic SQL to allow us to use field names based on the @PivotProperties parameter
SET @SQL =
'SELECT * INTO ##PRPropertiesPivot FROM (
SELECT
pvt.TestCaseId,
pvt.[Test Case],
pvt.Description,
CASE
WHEN pvt.Disposition = ''In Pre-Approval'' THEN ''Open''
WHEN pvt.Disposition = ''Pre-Approved'' THEN ''Open''
WHEN pvt.Disposition = ''In Test'' THEN ''Open''
WHEN pvt.Disposition = ''In Approval'' THEN ''Open''
WHEN pvt.Disposition = ''Approved'' THEN ''Closed''
ELSE pvt.Disposition
END AS ''Disposition'',
'''' AS ''BlankField''
FROM
(SELECT
Tc.TestCaseId,
Tc.TestCaseNumber,
Tc.PropertyName,
Tc.Disposition,
CAST(Tc.PropertyValue AS NVARCHAR(MAX)) AS PropertyValue
FROM
#Data AS Tc) AS Tc
PIVOT
(MIN(PropertyValue)
FOR PropertyName IN (' + @PivotProperties + ')) AS pvt'
EXEC (@SQL)
June 28, 2019 at 9:37 am
If you change EXEC (@SQL) to PRINT @SQL, do you still get the error? If not, the error is in the dynamic SQL. If you eyeball the @SQL output, you will probably be able to see what the syntax error is. If you can't, please posit it here.
John
June 28, 2019 at 9:40 am
I get this result. Still no closer to figuring it out.
SELECT * INTO ##PRPropertiesPivot FROM (
SELECT
pvt.TestCaseId,
pvt.[Test Case],
pvt.Description,
CASE
WHEN pvt.Disposition = 'In Pre-Approval' THEN 'Open'
WHEN pvt.Disposition = 'Pre-Approved' THEN 'Open'
WHEN pvt.Disposition = 'In Test' THEN 'Open'
WHEN pvt.Disposition = 'In Approval' THEN 'Open'
WHEN pvt.Disposition = 'Approved' THEN 'Closed'
ELSE pvt.Disposition
END AS 'Disposition',
'' AS 'BlankField'
FROM
(SELECT
Tc.TestCaseId,
Tc.TestCaseNumber,
Tc.PropertyName,
Tc.Disposition,
CAST(Tc.PropertyValue AS NVARCHAR(MAX)) AS PropertyValue
FROM
#Data AS Tc) AS Tc
PIVOT
(MIN(PropertyValue)
FOR PropertyName IN ([Description],[Test Case])) AS pvt
June 28, 2019 at 9:41 am
Should there be a closing bracket
SELECT * INTO ##PRPropertiesPivot FROM (
I cant find where it would sit
June 28, 2019 at 9:46 am
Maybe you just need to lose "FROM (" from the first line?
John
Edit - or do you just need a closing bracket at the very end?
June 28, 2019 at 9:52 am
Use PRINT or SELECT to return the value of @SQL and then debug your dynamic statement. Then you can propagate that to your statement that generates the dynamic statement.
On a different note wrapping a value with '[' + ColumnName + ']' doesn't make it safe from injection. Just like a single quote with a literal varchar a bracket can be escaped and injected into. As you're using SQL Server 2017 you can make use of STRING_AGG as well to make that statement far simpler:
SELECT @PivotProperties = STRING_AGG(QUOTENAME(rc.SettingValue), ',')
FROM dbo.Reporting_Configuration rc
WHERE rc.ReportName = 'Incident Summary'
AND rc.SettingName IN ('Test', 'Description');
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 28, 2019 at 7:22 pm
There are 7 opening brackets '(' but only 6 closing ')'. I think you need to add closing bracket at the end with alias.
--Vadim R.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply