/ Published in: SQL
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
DECLARE @reportname VARCHAR(500) = 'New PRD by Agency', @parm xml; SELECT @parm = CONVERT(xml,parameter) --as p, c.Name, c.Description,Path FROM dbo.Catalog c WHERE Name = @reportname SELECT c.Name AS ReportName,Description,Path, ParmValues.Parameter.value('(./Name)[1]','Varchar(100)')AS ParamName, ParmValues.Parameter.value( '(./Type)[1]','varchar(100)') AS DataType, ParmValues.Parameter.value( '(./Nullable)[1]','bit') AS NULLABLE, ParmValues.Parameter.value( '(./AllowBlank)[1]','bit') AS AllowBlank, ParmValues.Parameter.value( '(./MultiValue)[1]','bit') AS MultiValue, ParmValues.Parameter.value( '(./UsedInQuery)[1]','bit') AS UsedInQuery, ParmValues.Parameter.value( '(./Prompt)[1]','varchar(100)') AS Prompt, ParmValues.Parameter.value( '(./PromptUser)[1]','bit') AS PromptUser, ParmValues.Parameter.value( '(./DynamicDefaultValue)[1]','bit') AS DynamicDefaultValue FROM dbo.Catalog c JOIN @parm.nodes('//Parameter') AS ParmValues(Parameter) ON c.Name = @reportname